Help with Data Analysis

Question

Hi, I have &gt;17000 rows of data, where the data (rows) is grouped or associated with a single cell in the row above the section of data as I've tried to represent below. So rows 2 &amp; 3 are associated with the A1, row 6 is associated with A5 and so on. The number of rows differs for each group, there is no consistency at all.&nbsp;How can I take the value (e.g. A1 and A5) that precedes the section it is linked to and add it to a new column and repeat it for the given number of rows, so that I can eventually pivot the data? There is a blank row between each section of data.&nbsp;&nbsp;ABCD...V1&lt;value1&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;2Details associated&nbsp;with &lt;value1&gt;&nbsp;&nbsp;&nbsp;&nbsp;3More details&nbsp;associated with&nbsp;&lt;value1&gt;&nbsp;&nbsp;&nbsp;4&lt;blank&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;5&lt;value2&gt;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;6Details associated&nbsp;with &lt;value2&gt;&nbsp;&nbsp;&nbsp;&nbsp;...&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;17100&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Hope that makes sense! Any help would be appreciated.

riny_van_eekelen · Answer

Reme1000&nbsp;That would be a job for Power Query. The attached workbook contains an example of what it can do, with very little code writing.&nbsp;The basic steps are to connect to a table, add a custom column (here you need to type a formula similar to an Excel IF function, but simpler), fill-down and unpivot.&nbsp;If you are not familiar with Power Query, you may want to have look here first:https://exceloffthegrid.com/power-query-introduction/&nbsp;Perhaps a bit overwhelming at first but you will soon get the hang of it.

Forum Discussion

Help with Data Analysis

1 Reply

Resources

	A	B	C	D	...	V
1	<value1>
2	Details associated	with <value1>
3	More details	associated with	<value1>
4	<blank>
5	<value2>
6	Details associated	with <value2>
...
17100