Text string manipulation

i have a column of text strings in cells h13:h40. They all 2 letter state abbreviations, and many repeat several times, but not continuously. Im a truck driver and i record my odometer reading when i cross state lines to track how many miles i drive in every state for fuel tax purposes. I want to create a column in another area of the same spreadsheet L29:L43 that will be a list the states I've driven in. With no duplicates. Ill use these cells to figure total using sumif function based upon the first set of data. Any recommendations on how do this without horribly complex if formulas.

Thank you for the suggestion. But im trying to work within an existing form from my employer. It is designed to be filled out by hand and im tired of doing a billion calculations every friday. Ive got it working now, except that got a long list of states in the lower right of the sheet and i was hoping to be able to make excel fill in a column of cells with a list of states but skipping duplicates.

Try this... simply list all 50 states with their abbreviations, then use a SUMIF function on the abbreviation...

SUMIF.xls

Please let me know if you need help breaking this down...

Thanks,
Dennis

I suggest you remove your email address, unless you want to receive a load of spam.

You can extract a unique list of states from a long list using Advanced Filter. Debra Dalgleish explains how on her website:

http://www.contextures.com/xladvfilter01.html#ExtractWs

Hope this helps.

Pete

EDIT - Dennis, I'm following you this time !!

Oops, I think I misunderstood your question...

This is how I would copy over the unique states...

Copy all the states in Col H to Col L (include headings if needed).

Then highlight all the cells in Col L, then on the Data ribbon, select the Remove Duplicates...

This should give you the unique states, then add the SUMIF function...

Hope that helps, sorry for the confusion...

- Dennis

[Pete - HAHA!]

I wish my laptop could hookup to wifi right now. Ill try and explain a little better. On my form i have a range of cells c13:k40. In this range column c is the date i cross a state line, columns c through g have all been merged with c. I need to fix that because its unnecessary. Column h is where i enter the abbr for the state im entering. Column i is my odometer reading when i cross the state line column j contains formulas that figure how many miles i drove in each state. (Ive got that working). And column k is a recording of the road im driving on when i cross the state line.

In columns L M & N there are sections for fuel ( rows 13 thru 21). Def ( diesel exhaust fluid ). My truck doesnt use this do i dont use these cells. ( rows 22 thru 26
). and finally the totals section. This is where i need help.

Column L is a list of state abbreviations. And this is where im having trouble. Im already using sumif in columns m and n in this section to tally total miles in each state from the main portion of the page. My formula even references the text string in L column for comparison so i can manually enter say ky in L29 and the formula in m29 (=sumif(h13:h40,L29,j13:j40) ) and the formula will automatically tally all miles driven in ky. Similar formula in column n tallies fuel purchased by state using different data.

My problem is that i cant figure out how to make excel fill in column L with state abbreviations automatically as i enter date into column c.

Thank you for help by the way. I appreciate it.

I tried pete_uk suggestion. But when i hit ok it tells me the extract range has a missing or illegal field name. What is a field name?

That's the name of the column (or field) of data that you want to extract data from.

It's a pity you can't upload your file - maybe there will be wifi at your next stop.

Pete

To use the Advanced Filter that Pete suggested, make sure to do the following:
1. open the Advanced Filter pop-up box
2. choose Copy to another location
3. under List range: type your range (h13:h40 or whatever range)
4. under Copy to: type L29 (the cell where you want it to start)
5. check Unique records only

Thank you for your help. Ill try again when i have wifi available. This will drive me crazy till i can figure it out.