Each morning at work I run a Purchase order register report in Global Shop (the software we use) and I export a CSV file. When I open it in Excel, there are 4 columns that I isolate and reformat so I can sort the purchases according to plant location. Then I sum the totals. I created a Macro to do the formatting part for me. (text to columns, etc)

Now the part that has me stumped is...I am trying figure out conditional formatting or a formula to insert in the macro that will enter the plant name according to the account number.

So my file starts out with account numbers that look like this: 73500-B002

The letter B tells me the location. In my macro I used text to columns to separate the two parts of the account number so I have 73500 in one cell and B002 in the cell next to it, so I can sort.

Some account numbers do not have a letter and look like this: 73500-002, I can tell by the value in the last three digits. So it is the second column that tells me the location.

I started my macro at the beginning with unformatted CSV and I went through all the steps to isolate my data and then I stop recording.

So bottom line is, what can I do now, that I can add to my macro, a formula or rule or condition, to automatically fill in the plant location?

Like for example some formula that says: If the value starts with B, then return this value or if the value is between 004-037 then return this value?

I know that is the IF function, but how does it work when there are many variations?

I am very new to formulas and macros, so forgive me if I am not presenting this clearly. I am attaching the document.

Attached Files

050918.xlsm (16.5 KB, 10 views)