Convert multiple rows of horizontal data into three rows of vertical data

**msutton** · 06-10-2015, 01:12 AM

Hello - Long time stalker, first time poster. I've got an issue concerning over 30,000 items that I can't research and figure out simply because I can't explain it.

I need to convert this:

A B C D E F G H I J K
1 SKU ITEM1 QTY1 ITEM2 QTY2 ITEM3 QTY3 ITEM4 QTY4 ITEM5 QTY5
2 01CNBP208B1B1 BTCNBP208D01 1
3 01CNBP208B2B1 BTCNBP208D01 2
4 01CNBP208B3B1 BTCNBP208D01 3
5 01CNBP208B4B1 BTCNBP208D01 4
6 01CNBP208BC1B1 BTCNBP208D01 1 CHCNBP208D01 1
7 01CNBP208BC2B1 BTCNBP208D01 2 CHCNBP208D01 1
8 01CNBP208KB1B1 BTCNBP208D01 1 LCK5 1 MEMCASE 1 CRSD 1
9 01CNBP208KB2B1 BTCNBP208D01 2 LCK5 1 MEMCASE 1 CRSD 1
10 01CNBP208KB3B1 BTCNBP208D01 3 LCK5 1 MEMCASE 1 CRSD 1
11 01CNBP208KB4B1 BTCNBP208D01 4 LCK5 1 MEMCASE 1 CRSD 1
12 01CNBP208KBC1B1 BTCNBP208D01 1 CHCNBP208D01 1 LCK5 1 MEMCASE 1 CRSD 1
13 01CNBP208KBC2B1 BTCNBP208D01 2 CHCNBP208D01 1 LCK5 1 MEMCASE 1 CRSD 1
14 01CNBP208KBC3B1 BTCNBP208D01 3 CHCNBP208D01 1 LCK5 1 MEMCASE 1 CRSD 1

Into this:
A B C
1 SKU ITEM QTY
2 01CNBP208B1B1 BTCNBP208D01 1
3 01CNBP208B2B1 BTCNBP208D01 2
4 01CNBP208B3B1 BTCNBP208D01 3
5 01CNBP208B4B1 BTCNBP208D01 4
6 01CNBP208BC1B1 BTCNBP208D01 1
7 01CNBP208BC1B1 CHCNBP208D01 1
8 01CNBP208BC2B1 BTCNBP208D01 2
9 01CNBP208BC2B1 CHCNBP208D01 1
10 01CNBP208BC3B1 BTCNBP208D01 3
11 01CNBP208BC3B1 CHCNBP208D01 1
12 01CNBP208BC4B1 BTCNBP208D01 4
13 01CNBP208BC4B1 CHCNBP208D01 1
14 01CNBP208C1B1 CHCNBP208D01 1
151 01CNBP208KB1B1 BTCNBP208D01 1
16 01CNBP208KB1B1 LCK5 1
17 01CNBP208KB1B1 MEMCASE 1
18 01CNBP208KB1B1 CRSD 1
19 01CNBP208KB2B1 BTCNBP208D01 2
20 01CNBP208KB2B1 LCK5 1
21 01CNBP208KB2B1 MEMCASE 1
22 01CNBP208KB2B1 CRSD 1

Can anyone help me out? Would save me endless frustration

**msutton** · 06-10-2015, 01:24 AM

I've managed to put the following together. This formula gives me what I need for the first chunk of it, but instead of copying down all the data from B3, C3 etc to B4, C4 it goes to B8 C8 because it's five rows down.... See below...
A B C
1 =OFFSET(CANON!$A$3,INT((ROWS($A$1:A1)-1)/5),) =IF(ISBLANK(CANON!B3),"",CANON!B3) =IF(ISBLANK(CANON!C3),"",CANON!C3)
2 =OFFSET(CANON!$A$3,INT((ROWS($A$1:A2)-1)/5),) =IF(ISBLANK(CANON!D3),"",CANON!D3) =IF(ISBLANK(CANON!E3),"",CANON!E3)
3 =OFFSET(CANON!$A$3,INT((ROWS($A$1:A3)-1)/5),) =IF(ISBLANK(CANON!F3),"",CANON!F3) =IF(ISBLANK(CANON!G3),"",CANON!G3)
4 =OFFSET(CANON!$A$3,INT((ROWS($A$1:A4)-1)/5),) =IF(ISBLANK(CANON!H3),"",CANON!H3) =IF(ISBLANK(CANON!I3),"",CANON!I3)
5 =OFFSET(CANON!$A$3,INT((ROWS($A$1:A5)-1)/5),) =IF(ISBLANK(CANON!J3),"",CANON!J3) =IF(ISBLANK(CANON!K3),"",CANON!K3)
6 =OFFSET(CANON!$A$3,INT((ROWS($A$1:A6)-1)/5),) =IF(ISBLANK(CANON!B8),"",CANON!B8) =IF(ISBLANK(CANON!C8),"",CANON!C8)
7 =OFFSET(CANON!$A$3,INT((ROWS($A$1:A7)-1)/5),) =IF(ISBLANK(CANON!D8),"",CANON!D8) =IF(ISBLANK(CANON!E8),"",CANON!E8)
8 =OFFSET(CANON!$A$3,INT((ROWS($A$1:A8)-1)/5),) =IF(ISBLANK(CANON!F8),"",CANON!F8) =IF(ISBLANK(CANON!G8),"",CANON!G8)
9 =OFFSET(CANON!$A$3,INT((ROWS($A$1:A9)-1)/5),) =IF(ISBLANK(CANON!H8),"",CANON!H8) =IF(ISBLANK(CANON!I8),"",CANON!I8)
10 =OFFSET(CANON!$A$3,INT((ROWS($A$1:A10)-1)/5),) =IF(ISBLANK(CANON!J8),"",CANON!J8) =IF(ISBLANK(CANON!K8),"",CANON!K8)

A little clarification on the above ... My data is located in a sheet named Canon. My data starts on row 3. The "SKU"s are in column B, and the items / qty alternate from column C through K

Any help is appreciated

**:) Sixthsense :)** · 06-10-2015, 02:03 AM

It can be easily achieved by using VBA instead of using a complex formula.

Please confirm whether VBA solution is Okay for you or you want to do it in formula only?

**msutton** · 06-10-2015, 02:09 AM

Originally Posted by :) Sixthsense :)

It can be easily achieved by using VBA instead of using a complex formula.

Please confirm whether VBA solution is Okay for you or you want to do it in formula only?

Unfortunately I don't know heads or tails from VBA, but if it's that much easier I can do some googling and apply your solution once I figure it out.

**Glenn Kennedy** · 06-10-2015, 02:48 AM

OK. here's a complex solution. By PM, you've advised me of a smpler way to do column A. Great - use it. Now you have two choices:

EITHER: copy and paste values (to remove formulae) and filter on column B, deleting all rows with a zero;

OR: copy the non-zero rows to another sheet. If you need a hand with that - let me know.

**:) Sixthsense :)** · 06-10-2015, 03:27 AM

How to install your new code

Copy the Excel VBA code
Select the workbook in which you want to store the Excel VBA code
Press Alt+F11 to open the Visual Basic Editor
Choose Insert > Module
Edit > Paste the macro into the module that appeared
Close the VBEditor
Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

Please Login or Register  to view this content.

To run the Excel VBA code:

Press Alt-F8 to open the macro list
Select a macro in the list
Click the Run button

Attached a sample file for your easy reference

Note: Please don't waste our time by way of sending ping messages to many persons. Even me too received ping message for this one and wasted my time in writing this code.