when collecting my data I organised my variables wrong but it is not a transpose issue. I tried xlookup but one of my arrays is a row instead of a column. I suspect it might be an index job but I am not familiar with it at all.
This is what it currently looks like:
| Company Name | Time Point (t1) | Time Point (t2) | Time Point (t3) | Time Point (t4) | Time Point (t5) | Time Point (t6) |
|---|---|---|---|---|---|---|
| company name(1) | x | x | x | x | x | x |
| company name(2) | ||||||
| company name(3) | ||||||
| company name(4) |
what I need is:
| Company Name | Time Point | Figure |
|---|---|---|
| company name(1) | t(1) | X |
| company name(1) | t(2) | X |
| company name(1) | t(3) | X |
| company name(1) | t(4) | X |
| company name(1) | t(5) | X |
| company name(1) | t(6) | X |
| company name(2) | t(1) | X |
| company name(2) | t(2) | X |
| company name(2) | t(3) | X |
| company name(2) | t(4) | X |
| company name(2) | t(5) | X |
| company name(2) | t(6) | X |
| company name(3) | t(1) | X |
| company name(3) | t(2) | X |
| company name(3) | t(3) | X |
| company name(3) | t(4) | X |
| company name(3) | t(5) | X |
| company name(3) | t(6) | X |
| company name(4) | t(1) | X |
| company name(4) | t(2) | X |
| company name(4) | t(3) | X |
| company name(4) | t(4) | X |
| company name(4) | t(5) | X |
| company name(4) | t(6) | X |
I HOPE THIS HELPS.
THANKS IN ADVANCE
Ive tried xlookup but im not that advanced. the alternative is doing it manually but its a large dataset.
>Solution :
Your data is pivoted, and you want to unpivot it. The best way to do this is in Power Query.
Highlight your data, go to Data, Get & Transform Data from Table/Range. This will turn your data into a table and launch the Power Query editor.
In the Power Query editor, right-click the Company Name column, and click Unpivot Other Columns.
In the image below, the blue table is the original data, the green table is the output after unpivoting.
You can also use Replace Values to get rid of the extra "Time Point" text if you like.
