Split a number in a cell into that many number of rows

[Jmiller92]

Hi all,

New here and just want to say thank you in advance for taking the time to look at this and help if possible.

I want to transform some aggregated data into single 1 unit rows. So let's say you have a cell that contains the number 100 in it - is it possible to convert this quickly into 100 rows containing 1?

Many thanks,

Josh

[johnbasha33 · Accepted solution]

@Jmiller92 

Yes, it's possible to transform aggregated data into individual rows using various tools like Excel formulas, Power Query, or scripting languages. Here's how you can achieve it using Excel formulas:

Assuming your aggregated data is in cell A1 containing the number 100, and you want to generate 100 rows with the value 1 in column B, starting from row 2, you can use the following formula in cell B2:

```excel
=IF(ROW()-1<=$A$1, 1, "")
```

Then drag this formula down for 100 rows. This formula checks if the row number minus 1 is less than or equal to the value in cell A1 (100 in this case). If true, it returns 1; otherwise, it returns an empty string.

After dragging down the formula for 100 rows, you'll have 100 rows with the value 1 in column B.

Alternatively, you can achieve the same using Power Query in Excel. Here's how:

1. Select your aggregated data.
2. Go to the "Data" tab and click on "From Table/Range" to load the data into Power Query.
3. In Power Query Editor, add a custom column with the formula `List.Repeat({1}, [Column1])`. Replace `[Column1]` with the name of your aggregated column.
4. Expand the custom column to create individual rows.
5. Load the transformed data back into your Excel worksheet.

Using Power Query offers more flexibility and scalability if you're dealing with larger datasets or need to perform more complex transformations.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!