Switch columns to rows using value in one of the columns

[analyticsneed]

Hi,

I need to switch data in my columns into rows by using the data type value in one of the columns. Each combination of 3 rows in my data has sales, quantity and price of a certain item# for 52 weeks in the year. So for each item# I have 3 rows (datatype = sales, quantity and price) and 54 columns (item#, datatype, and 52 week columns). Instead I need to have 52 rows for each item# with 5 columns (item#, week#, sales, quantity, price). Can you suggest a excel formula or a macro to do this? Should I be considering access since the data volume is pretty large?

The attachment has an example of what I am looking for.

Thanks a lot,
Gavin

[arlu1201]

Try this code

Please Login or Register  to view this content.

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
Choose Insert | Module
Where the cursor is flashing, choose Edit | Paste

To run the Excel VBA code:
Choose View | Macros
Select a macro in the list, and click the Run button

[analyticsneed]

Hi Arlette -

Thanks for your help. That worked perfectly with the example I posted. However, I am running into the following issues when I ran the code on real data:

1. Some of the item numbers don't have all three rows. For e.g. some of them just had the POS Sales and POS Qty amounts.
2. I am running out of excel row count limit when I run this with the real data. I have about 25000 item nbrs. So 25000 x 52 weeks = 1.3million records.

Questions:
1. Can we do a join/lookup for specific data types while populating the target rows so that it works even if the data isn't sorted and/or some item numbers don't have all 3 data types.
2. Can you provide a version of this code that can work on ms access so that I can load the data into tables and execute the macro from ms access? (I am hoping this way I can tackle the max rowcount issue)

Thanks,
Gavin

[arlu1201]

The code will need to be re-worked to take care of non-standard format like you mentioned in point 1.

I have never worked on MS Access programming so wont be able to help you that way, sorry. However, can you re-work on your format so that the data fits in the max rows that excel provides?

[analyticsneed]

Sure. I can break down the list into two portions so that it's within the excel row count limit. I beleive the limit is about 1million. Can you please help with Issue #1 above. Thanks a ton.

[icestationzbra]

a formulaic approach, which certainly will not alleviate the issue with row-limit: