0

I want to convert:

enter image description here

From a list of columns that has a common label, to a row where labels are not repeated and all the values are listed under them. Is there a way to do it ?

Ron Rosenfeld
  • 8,198
  • 3
  • 13
  • 17
Naval Kishore
  • 1
  • Yes, manually. Excel does not really have an easy way to do this. You can write a Macro, but that takes as much time or longer than to do it manually. – LPChip Oct 21 '21 at 09:47
  • It might be easier to find out if you can get this horizontal list from the source you got the vertical list from in the first place. – LPChip Oct 21 '21 at 09:48
  • This is csv generated from a log from Ubuntu, I would go with python route to parse if excel is not able to do so... – Naval Kishore Oct 21 '21 at 09:59
  • 1
    Does this answer your question? [Partially transpose data which pasted as a single column](https://superuser.com/questions/1461107/partially-transpose-data-which-pasted-as-a-single-column) – Scott - Слава Україні Oct 21 '21 at 19:12

1 Answers1

0

Assuming

  • data is in columns A:B

  • results to start in E9

  • Your version of Excel has UNIQUE and FILTER functions

    E9:  =TRANSPOSE(UNIQUE(FILTER($A:$A,$A:$A<>"")))
E10: =FILTER($B:$B,$A:$A=E$9)

Select E10 and fill right as far as needed

If you don't have those functions, I would suggest Power Query or VBA to perform the data transformation.

Ron Rosenfeld
  • 8,198
  • 3
  • 13
  • 17
  • You should avoid referencing entire columns with such constructions. Each of your formulas has to parse more than a million row-level calculations. If there's only data as far as, say, row 100, that's a huge overhead. – Jos Woolley Oct 24 '21 at 05:21
  • @JosWoolley What you write does apply to certain functions, eg: `VLOOKUP` with the last argument = `FALSE`; SUMPRODUCT, array formulas and so forth. Other functions (eg `SUMIF, COUNTIF`) are optimized so they recognize the last cell in the column, and will work efficiently given whole column arguments. I suspect (but not certain) `FILTER` is also so optimized. My preference would be to use **Tables** and structured references, but that seemed a discussion beyond the scope of the question. Since **Tables** became available, I try to avoid partial range references where the range might vary. – Ron Rosenfeld Oct 24 '21 at 11:33
  • You can easily check. Close all workbooks, open a new workbook and place the number 1 in cell `A1` then the formula `=FILTER($B:$B;$A:$A=1)` in cell `J1` and then copy to a modest number of cells, say 500, e.g. `J1:AC25`. And then watch Excel churn away. No optimisation there. Repeat the experiment with `=FILTER($B$1:$B$100;$A$1:$A$100=1)` and compare. Each and every one of the cells referenced in the include parameter is being evaluated against the specified condition, in the entire column version that's 1048576 calculations. – Jos Woolley Oct 24 '21 at 11:46
  • Actually I have a feeling that VLOOKUP with a final parameter of FALSE is optimised to calculate over the used range only. Of course, it's still far less efficient than the binary search version, but that's not the point. MATCH and the IFS family of functions are also optimised in that they calculate over the used range only. – Jos Woolley Oct 24 '21 at 11:52
  • In a completely blank worksheet, `=MATCH(1,$A:$A,0)`, placed in 500 cells, calculates instantly. Clearly half a billion cells are not being evaluated! Whereas they most certainly are with the FILTER function I gave earlier. – Jos Woolley Oct 24 '21 at 11:56
  • @JosWoolley There may be something else going on in your particular example (which won't run on my machine). If you use the worksheet information and formulas as given, and time the `Worksheet.Calculate` function using VBA, repeating it 100 times, there is no significant time difference using whole column range vs partial column: `0.544050` vs `0.543893` seconds on one pair of runs – Ron Rosenfeld Oct 24 '21 at 12:59
  • To which formula are you referring? – Jos Woolley Oct 24 '21 at 14:49
  • @JosWoolley `=FILTER($B:$B;$A:$A=1)` I'm sorry. This discussion, while interesting, is far enough afield of the original question that I will bow out at this point. I'm sure if the OP has a problem, he will let me know and adjustments can be made. As I wrote, my first choice would be Tables. If that is not available in the Excel version, then a dynamic range name. – Ron Rosenfeld Oct 24 '21 at 14:56