1

I have some data that has been output by an accounting system, but needs massaging. Each Invoice is output on a row, but the categories of the products on the invoice are output as a column - I want all the data to be on a row.

Excel Sample Image

(I've just discovered that as a new user to here I can't post an image, so this link links to a jpg that explains what I mean)

So the image shows how the data is currently output and how it needs to be. I assume that I need to use VBA to do this (there's actually 1000's of rows of data and a new sheet is generated every day), but I'm interested in any other ideas or samples of what functions to use in VBA (I'm a delphi developer and haven't written anything in VBA for many years, but I'm sure I can turn my hand to it with a few hints!)

Many Thanks

David

Community
  • 1
  • 1
David Rose
  • 130
  • 1
  • 8
  • 1
    How many _distinct_ types in Column G? Will there be a certain range, or it's totally dynamic? – Passerby Sep 11 '12 at 15:44
  • 4
    If you follow the "fillempty" steps outlined here - http://dmcritchie.mvps.org/excel/fillempt.htm you can then use a pivot table for this. – Tim Williams Sep 11 '12 at 16:16
  • @Passerby at the moment it's about 15. It's a config setting in the software that exports, so it could go up but if a VBA routine was involved, that could be changed at the same time. – David Rose Sep 12 '12 at 08:49
  • @DavidRose VBA sucks at working with arrays, and `Dictionary` is kind of slow, so if the product type is dynamic and you have many data, I would suggest you to try what Tim said: fill the blank and use pivot table. It's still possible in VBA though. Do you really want an VBA solution anyway? – Passerby Sep 12 '12 at 09:14
  • @Passerby - I've come up with a completely "out of the box" idea to fix the problem, might work, might not - will know in a day or two :) – David Rose Sep 12 '12 at 11:48

0 Answers0