Excel - multiple columns into two columns with primary key

Question

I have multiple columns with an identifier in the first row for each.
Each could have a different number of entries.
COL 1 user1 user 2 user3 user4 ROW 1 data1 data1 data1 data1 ROW 2 data2 data2 data2 data2 ROW 3 data3 data3 data3 data3 ROW 4 data4 data4 data4 ROW 5 data5 data5

I would like to have all the data in ONE column, with an identifier for the previous column name.eg:
COL 1 user data ROW 1 user1 data1 ROW 2 user1 data2 ROW 3 user1 data3 ROW 4 user1 data4 ROW 5 user1 data5 ROW 6 user2 data1 ROW 7 user2 data2 ROW 8 user2 data3 ROW 9 user3 data1 ROW 10 user3 data2 ROW 11 user3 data3 ROW 12 user3 data4 ROW 13 user3 data5

Assistance would be Greatly appreciated

Community · Accepted Answer · 2017-05-23 12:06:29Z

0

You might apply the process described in detail here, filter the Table to select and delete blanks from Value column, Sort by Column then Value, enter in A2 and copy down to suit:

="ROW "&ROW()-1

then change the headings and if desired select all, Copy, Paste Special, Values and Convert to Range.

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered Feb 18, 2015 at 11:28

pnuts

59.7k11 gold badges92 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Bilal Essa Over a year ago

Hi there, Thanks you for the assistance and insight. I think this would not have worked as the data is text and not numerical so when making a pivot that is lost. Also had quite a large set >2000 columns and pivot table only allows 250ish. Thanks again

lori_m Over a year ago

@BilalEssa Just to add this approach can handle text and is fast even on many thousands of rows. If you're familiar with pivots you can also add value as a row field and unselect blanks before double-clicking the total. See here for a similar method: stackoverflow.com/questions/11674964/….

lori_m Over a year ago

No worries, a method often needs posting multiple times before it catches on. Seems others have benefitted even if it's too late for this one.

XOR LX · Accepted Answer · 2015-02-18 11:46:26Z

Alternatively, if you prefer a formula-based solution, then, assuming that the table as you give it is in A1:D6 (with headers in row 1), enter this array formula** in F2:

=IF(ROWS($1:1)>COUNTA($A$2:$D$6),"",INDEX($A$1:$D$1,MATCH(TRUE,MMULT(SUBTOTAL(3,OFFSET($A$2:$A$6,,COLUMN($A$2:$D$6)-MIN(COLUMN($A$2:$D$6)))),N(COLUMN($A$2:$D$6)>=TRANSPOSE(COLUMN($A$2:$D$6))))>=ROWS($1:1),0)))

and copy down until you start to get blanks for the results.

Then enter this (non-array) formula in G2:

=IF(F2="","",INDEX(INDEX($A$2:$D$6,,MATCH(F2,$A$1:$D$1,0)),COUNTIF(F$2:F2,F2)))

and copy down appropriately.

Note that this set-up assumes two things:

1) That any blank cells within a given column will only ever occur at the end of that column; there will never be any blank cells in-between non-blank entries.

2) That any blank cells within the range are "genuinely" blank; not, for example, the "" as a result of formulas in those cells.

Regards

**Array formulas are not entered in the same way as 'standard' formulas. Instead of pressing just ENTER, you first hold down CTRL and SHIFT, and only then press ENTER. If you've done it correctly, you'll notice Excel puts curly brackets {} around the formula (though do not attempt to manually insert these yourself).

Hi there Thank you so much, conceptually it works perfectly, though my data set is a 'bit' larger and i have been playing around with the range values in the formula to no avail. How would i substitute the ranges if my set was, 2926 columns, and 150 rows.
Ah. That's nearly half a million cells! Quite a difference! (And probably something you should have mentioned to begin with?) I'm afraid that at that size, and using the set-up I posted, Excel will run out of resources (=crash) way before even coming close to finishing the task. Perhaps a VBA solution will be your best bet.
Shucks, sorry about the ambiguity. Do you think there would be any chance even if I split the data set up, into say chunks of 500 columns?
Perhaps. Might depend on what else you've got going on in that file which is eating up resource. Perhaps you could have a go and see?
Thank You so much. though how would I edit =IF(ROWS($1:1)>COUNTA($A$2:$D$6),"",INDEX($A$1:$D$1,MATCH(TRUE,MMULT(SUBTOTAL(3,OFFSET($A$2:$A$6,,COLUMN($A$2:$D$6)-MIN(COLUMN($A$2:$D$6)))),N(COLUMN($A$2:$D$6)>=TRANSPOSE(COLUMN($A$2:$D$6))))>=ROWS($1:1),0))) the formula to make it work for say xHundred columns (lets try 300 to start off with)

Collectives™ on Stack Overflow

Excel - multiple columns into two columns with primary key

2 Answers 2

3 Comments

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

8 Comments

Linked

Related