Expand data line by line - complicated question

nostradamus

Board Regular
Joined
Aug 9, 2010
Messages
143
Office Version
  1. 365
Platform
  1. Windows
Hi folks,
Below is an excel table ColA has the Date, County, and Location concatenated; and ColB has the number of birds observed at that location.
I used Multiple Consolidation Range in excel to get to this point from tabular data format.

AB
1UniqueIDValue
242787_Bailey_a3
342901_Handerson_a2
442901_Handerson_b4
542901_Handerson_c5
642796_Atascosa_a2
742796_Atascosa_b5
842796_Atascosa_c7

<tbody>
</tbody>

Question: Now, for the uniqueID in Row2 (42787_Bailey_a), there were 3 birds observed.
I need to find a way to spread the 3 out into 3 rows - as shown in the table below.
Is there a way to do it, other than manually spreading it out. I have about 3000 rows with varying values to spread out.

AB
1UniqueIDValue
242787_Bailey_a1
342787_Bailey_a1
442787_Bailey_a1
542901_Handerson_a1
642901_Handerson_a1
7..
8..

<tbody>
</tbody>


A Big Thanks.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
maybe

UniqueIDValueUniqueIDValueCustom
42787_Bailey_a
3​
42787_Bailey_a
3​
1​
42901_Handerson_a
2​
42787_Bailey_a
3​
2​
42901_Handerson_b
4​
42787_Bailey_a
3​
3​
42901_Handerson_c
5​
42901_Handerson_a
2​
1​
42796_Atascosa_a
2​
42901_Handerson_a
2​
2​
42796_Atascosa_b
5​
42901_Handerson_b
4​
1​
42796_Atascosa_c
7​
42901_Handerson_b
4​
2​
42901_Handerson_b
4​
3​
42901_Handerson_b
4​
4​
42901_Handerson_c
5​
1​
42901_Handerson_c
5​
2​
42901_Handerson_c
5​
3​
42901_Handerson_c
5​
4​
42901_Handerson_c
5​
5​
42796_Atascosa_a
2​
1​
42796_Atascosa_a
2​
2​
42796_Atascosa_b
5​
1​
42796_Atascosa_b
5​
2​
42796_Atascosa_b
5​
3​
42796_Atascosa_b
5​
4​
42796_Atascosa_b
5​
5​
42796_Atascosa_c
7​
1​
42796_Atascosa_c
7​
2​
42796_Atascosa_c
7​
3​
42796_Atascosa_c
7​
4​
42796_Atascosa_c
7​
5​
42796_Atascosa_c
7​
6​
42796_Atascosa_c
7​
7​

columns Value and Custom can be removed
 
Last edited:
Upvote 0
sandy666, that is what I want it to look like (Custom column will have just value 1, but as you said that can be replaced).
How did you get to that point. What steps did you take to get there.
 
Upvote 0
You can do that with PowerQuery aka Get&Transform

UniqueIDValueUniqueIDValue
42787_Bailey_a
3​
42787_Bailey_a
1​
42901_Handerson_a
2​
42787_Bailey_a
1​
42901_Handerson_b
4​
42787_Bailey_a
1​
42901_Handerson_c
5​
42901_Handerson_a
1​
42796_Atascosa_a
2​
42901_Handerson_a
1​
42796_Atascosa_b
5​
42901_Handerson_b
1​
42796_Atascosa_c
7​
42901_Handerson_b
1​
42901_Handerson_b
1​
42901_Handerson_b
1​
42901_Handerson_c
1​
42901_Handerson_c
1​
42901_Handerson_c
1​
42901_Handerson_c
1​
42901_Handerson_c
1​
42796_Atascosa_a
1​
42796_Atascosa_a
1​
42796_Atascosa_b
1​
42796_Atascosa_b
1​
42796_Atascosa_b
1​
42796_Atascosa_b
1​
42796_Atascosa_b
1​
42796_Atascosa_c
1​
42796_Atascosa_c
1​
42796_Atascosa_c
1​
42796_Atascosa_c
1​
42796_Atascosa_c
1​
42796_Atascosa_c
1​
42796_Atascosa_c
1​

Code:
[SIZE=1]// Table7
let
    Source = Excel.CurrentWorkbook(){[Name="Table7"]}[Content],
    Single1 = Table.AddColumn(Source, "Custom1", each 1),
    List = Table.AddColumn(Single1, "Custom", each {1..[Value]}),
    Expand = Table.ExpandListColumn(List, "Custom"),
    ROC = Table.SelectColumns(Expand,{"UniqueID", "Custom1"}),
    Rename = Table.RenameColumns(ROC,{{"Custom1", "Value"}})
in
    Rename[/SIZE]
 
Upvote 0
this is NOT vba
this is Power Query M-code
what is your Excel version?
 
Upvote 0
so select your source table (range)
from Data tab choose From Table (or Get Data - From Table), check My Table Has Headers
it will open Power Query Editor
go to Advanced Editor
replace code there with code from the post#4
be sure the name of the source table is the same as in this line: Source = Excel.CurrentWorkbook(){[Name="[B]Table7[/B]"]}[Content], (should be the same)
if not, change the name table in this line to your table name
then ok
Close&Load

my code is based on your example from the post#1
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,176
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top