Anyone any good with excel? (1 Viewer)

[pete]

I've a CSV file with 10,000+ rows. For the sake of simplicity lets say there are three columns in each row

Date
Counter
Textfield

The values in the textfield column in about 900 of the rows contain duplicates and I need some way of automatically finding and appending some standard text to the end these fields in the rows with the lowest counter AND the oldest date. Can this be done with a macro? Or some manipulation / comparison of additional counter columns?

Oh and in most cases there are just two duplicates, but some have between three and nine. I'm hoping that whatever voodoo used to find matching pairs can just be run N times until all dupes are gone.

So - anyone able to help?

 

[pete]

This is a CSV dump from a MySQL database so if there's some MySQL magic that can be done without involving database dumps, excel & database imports that would be good also.

 

[HMD]

The only people i know that are any use with it work in finance.

 

[pete]

fuck all y'all i'm doing it by hand. almost halfway through and i want to put my fist through this monitor.

 

[pete]

all done.

am now having one of those "I've made a terrible mistake" moments...

 

[dudley]

These guys are really helpful and will write you macros for free
http://www.excelforum.com/

 

[pete]

Yeah, thanks. A bit bleedin late.

 

[Mormon Nailer]

Sorry, I'd have done that for you no bother.

 

[Mormon Nailer]

For future reference the counter one is easy enough
Sort by textfield, then by date. let's say the textfield column is A and you are working in col D
Keep the top row blank

=IF(A1=A2,D1+1,1)
That gives you the counter that resets each time

You can add another col to concatenate that to the textfield, or else do it all in one go
=IF(A1=A2,CONCATENATE(A1,"-",D1+1),CONCATENATE(A1,"-",1))

If it's sorted by textfield and then by date the same approach can be used to append the first date after the textfield changes to each one.

For example - if your textfield is A and date is in B - use C to hold the oldest date
With A2 as the active cell
=IF(A2=A1,C1,B2)
So if the textfield is the same as the row above, look to the roe above for the date, every time the textfield changes look left for the date.

Again just concatenate everything together in another column.

 

[ann post]

ok more a google sheets question but it'll probably end up being excel based.

I'm trying to make a little food calculator thing so I can figure out how roughly balanced i'm eating.

Was thinking I'd make one form generated sheet which would be like, all the data on bananas or honey or whatever per 100g, this i can do.


the second sheet I want to have which would be the general interface would be a list of drop downs in a column, and when you select something from the column it will fill the row. This is the bit I don't know what functions I should be using for.

Not sure I can make this vaguer.

 

[jonah]

Not really a solution to your specific problem, but have you considered just using an app like myfitnesspal or lifesum?

 

[ann post]

Not really a solution to your specific problem, but have you considered just using an app like myfitnesspal or lifesum?

Ah not really - I've already strava running and prefer making stuff I can customise.

 

[Deadmanposting]

There is an Excel World Championships and an Irish guy is "the LeBron James of Excel"

Inside the World Excel Championships (Yes, You Read That Right)

The excitement is off the charts at the Olympics of competitive ‘spreadsheeting’

www.wsj.com

 

[ann post]

he came 3rd this time

To view this content we will need your consent to set third party cookies.
For more detailed information, see our cookies page.

Accept third party cookies

 

[Deadmanposting]

Different class

I am over the moon when I use CONCATENATE or the PROPER function

 

[Cormcolash]

he came 3rd this time

To view this content we will need your consent to set third party cookies.
For more detailed information, see our cookies page.

Accept third party cookies

To view this content we will need your consent to set third party cookies.
For more detailed information, see our cookies page.

Accept third party cookies