Need to rearrange items in row so all columns have the same data

[mvparker79]

I get this list of invoices and part of the list shows these Tracking ID Charge Descriptions (Fuel Surcharge, Saturday Delivery, Customs Duty, etc) and Tracking ID Charge amounts. Each invoice may have multiple charge descriptions. The problem is that instead of having a column header with the name of the charge and then the value of that charge on the invoice line, it puts the name of the charge on the same line in one column, then next to it it'll have the amount, then the next column will have another charge and the next column will have the amount. To complicate matters even further these rows don't have the charge descriptions line up in the same column. So there is no way for me to do any kind of vlookup or anything like that. I'm really not sure what the best way to go about organizing this data is.

[GeneralDisarray]

oh man... that's nuts!

What is this data coming from exactly? This could be fixed with VBA, but it shouldn't have to be. Does this come from a Sql database or somthing?

[mvparker79]

It is sent to us via FedEx. Stupid I know.

[GeneralDisarray]

Well, you could put it all into one table (attached is an example of that) and then maybe pivot from that table if you need a summary.

Have a look at the attached workbook. The code to make the simple table is below

Please Login or Register  to view this content.

[mvparker79]

This is great! What part do I need to change so that it offsets to the appropriate column in my actual file. The one I sent you had a lot of data removed.

[mvparker79]

Never mind. I figured it out! Thanks!!

[GeneralDisarray]

No problem, glad it worked out.

[mvparker79]

One last thing. I've moved a few things around and I'm wanting to add to the output tab the invoice date. I've gotten everything to work except for the fact that the value that it is bringing in is not in date format. Here is the section of the code where I'm getting my 'Type mismatch' error.

Please Login or Register  to view this content.

I've also tried:

Please Login or Register  to view this content.

[GeneralDisarray]

post up an example please.

[mvparker79]

I've changed up the code a bit to work with my original file before I edited it before sending. Right now, I'm just trying to work with the invoice date piece. What I would ultimately like to have in the Reorganized Data tab is the month of the invoice date so that the pivot table can be summarized by month.

Please Login or Register  to view this content.

[mvparker79]

Here is the new file I'm working with.

[GeneralDisarray]

Got it to work. The CDate() was the right idea, but you need to make a string to pass into that function (see below).

I used MID(), RIGHT(), and LEFT() to put this in the MM/DD/YYYY format (e.g., 4/25/2014). When you do that, the CDate() works properly.

Please Login or Register  to view this content.

[mvparker79]

Ha! That's nearly exactly what I've been working on while waiting. lol I decided that I'd like to have the first day of that month. I've been able to get the code to display the month name, however when I do the pivot the dates aren't sorted chronologically. So I've been trying to get the EOMonth function to work with this line:

Please Login or Register  to view this content.

But I get the following error:

"Unable to get the EoMonth property of the WorksheetFunction class"

Whatever that means...