Reposition data on sheet and calculate how many times it occurs

Hi,

I have a set of data which has an initail layout as below:

Cell separator = |

Order#1|Order#2|CompName|Date|Item#|Quantity|Price|TotalPrice

and after VBA would be changed to

CompName|
QuantityofItem#s|Order#1|Order#2|Date|
Item#|Quantity|Price|TotalPrice|

The above QuantityofItem#s would be calculated by the number of times the Order#1 appears

I have made a mistake on the attached Example.xls so please use the one on my next post further down the page as it shows on the tabs the data before and after applying VBA.

Thanks,

David

Why do you want to do this?
Your original data is in a more usable form than your requested result.

What determines 121228098 in C16?

See this workbook. It isn't a solution for your request, it is only a "visual" aid towards one.

Hi,

I agree with Marcol. Your original data is more easily analysed/summarised as it is.

If you really do want another format then have you considered a Pivot Table? Add a new row of column label headers with values A:H and then put E in the Row labels PT area, A, B & D in the column label area and E in the Values area.

Perhaps this macro could shuffle your data the way you wish.

Please Login or Register  to view this content.

Alf

See if this formula based workbook is of any use, it can be modified to return more than one table if needed.

Select from the drop-down in Sheet "Summary" B2