Text string manipulation

1. 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.

2. Re: Text string manipulation

Hi jswgames,

welcome to the forum.

suggest you to use pivot table... thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

3. Re: Text string manipulation

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.

4. Re: Text string manipulation

Id put the worksheet up here so it can be seen but my laptop doesnt have wifi here and im posting from my iphone

5. Re: Text string manipulation

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

6. Re: Text string manipulation

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

Hope this helps.

Pete

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

7. Re: Text string manipulation

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!]

8. Re: Text string manipulation

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.

9. Re: Text string manipulation

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?

10. Re: Text string manipulation

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

11. Re: Text string manipulation

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

12. Re: Text string manipulation

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

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1