0

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

Bilal Essa
  • 1
  • 1
  • 2

2 Answers2

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.

Community
  • 1
  • 1
pnuts
  • 58,317
  • 11
  • 87
  • 139
  • 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 – Bilal Essa Feb 18 '15 at 21:06
  • @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: http://stackoverflow.com/questions/11674964/how-can-i-transform-rows-into-repeated-column-based-data/11676403#11676403. – lori_m Feb 19 '15 at 22:49
  • 1
    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. – lori_m Feb 20 '15 at 07:02
0

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).

XOR LX
  • 7,632
  • 1
  • 16
  • 15
  • 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. – Bilal Essa Feb 18 '15 at 13:52
  • 1
    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. – XOR LX Feb 18 '15 at 14:54
  • 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? – Bilal Essa Feb 18 '15 at 15:18
  • 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? – XOR LX Feb 18 '15 at 15:22
  • 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) – Bilal Essa Feb 18 '15 at 15:34
  • Try having a go at working out to what each of the references in my formula needs to be changed, bearing in mind that, as I said, that was based on a range of A1:D6. For example, my $A$1:$D$1 clearly represents the top row in that range (i.e. which contains the headers to be returned). I'm sure you can work out the rest. – XOR LX Feb 18 '15 at 15:46
  • Thanks a Million, sorry I did try before asking, silly mistake was referencing the range instead of the header - sigh. Though was resource and time consuming, am finally done. Thanks a lot for the insight, how does this work, it is my first time posting, could I upvote you or something? – Bilal Essa Feb 18 '15 at 21:05
  • Yes. Afraid it is necessarily quite a resource-heavy set-up. Re upvoting, not entirely sure myself! Think you can click the up-arrow next to my answer if you want! Cheers. – XOR LX Feb 18 '15 at 21:41