Expand data line by line - complicated question

nostradamus

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
42901_Handerson_a
42901_Handerson_b

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: center"]UniqueID[/TD]
[TD="align: center"]Value[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: center"]42787_Bailey_a[/TD]
[TD="align: center"]3[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: center"]4[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: center"]42901_Handerson_c[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: center"]42796_Atascosa_a[/TD]
[TD="align: center"]2[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: center"]42796_Atascosa_b[/TD]
[TD="align: center"]5[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: center"]42796_Atascosa_c[/TD]
[TD="align: center"]7[/TD]

</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
42787_Bailey_a
42787_Bailey_a

<tbody>
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]1[/TD]
[TD="align: center"]UniqueID[/TD]
[TD="align: center"]Value[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]2[/TD]
[TD="align: center"]42787_Bailey_a[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]3[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]4[/TD]

[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]5[/TD]
[TD="align: center"]42901_Handerson_a[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]6[/TD]
[TD="align: center"]42901_Handerson_a[/TD]
[TD="align: center"]1[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]7[/TD]
[TD="align: center"].[/TD]
[TD="align: center"].[/TD]

[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=CACACA]#CACACA[/URL] , align: center"]8[/TD]
[TD="align: center"].[/TD]
[TD="align: center"].[/TD]

</tbody>


A Big Thanks.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
maybe

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]UniqueID[/td][td=bgcolor:#5B9BD5]Value[/td][td][/td][td=bgcolor:#70AD47]UniqueID[/td][td=bgcolor:#70AD47]Value[/td][td=bgcolor:#70AD47]Custom[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]42787_Bailey_a[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td=bgcolor:#E2EFDA]42787_Bailey_a[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]42901_Handerson_a[/td][td]
2​
[/td][td][/td][td]42787_Bailey_a[/td][td]
3​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]42901_Handerson_b[/td][td=bgcolor:#DDEBF7]
4​
[/td][td][/td][td=bgcolor:#E2EFDA]42787_Bailey_a[/td][td=bgcolor:#E2EFDA]
3​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]42901_Handerson_c[/td][td]
5​
[/td][td][/td][td]42901_Handerson_a[/td][td]
2​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]42796_Atascosa_a[/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td=bgcolor:#E2EFDA]42901_Handerson_a[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]42796_Atascosa_b[/td][td]
5​
[/td][td][/td][td]42901_Handerson_b[/td][td]
4​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]42796_Atascosa_c[/td][td=bgcolor:#DDEBF7]
7​
[/td][td][/td][td=bgcolor:#E2EFDA]42901_Handerson_b[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42901_Handerson_b[/td][td]
4​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42901_Handerson_b[/td][td=bgcolor:#E2EFDA]
4​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42901_Handerson_c[/td][td]
5​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42901_Handerson_c[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42901_Handerson_c[/td][td]
5​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42901_Handerson_c[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42901_Handerson_c[/td][td]
5​
[/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_a[/td][td=bgcolor:#E2EFDA]
2​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_a[/td][td]
2​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_b[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_b[/td][td]
5​
[/td][td]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_b[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_b[/td][td]
5​
[/td][td]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_b[/td][td=bgcolor:#E2EFDA]
5​
[/td][td=bgcolor:#E2EFDA]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_c[/td][td]
7​
[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_c[/td][td=bgcolor:#E2EFDA]
7​
[/td][td=bgcolor:#E2EFDA]
2​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_c[/td][td]
7​
[/td][td]
3​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_c[/td][td=bgcolor:#E2EFDA]
7​
[/td][td=bgcolor:#E2EFDA]
4​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_c[/td][td]
7​
[/td][td]
5​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_c[/td][td=bgcolor:#E2EFDA]
7​
[/td][td=bgcolor:#E2EFDA]
6​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_c[/td][td]
7​
[/td][td]
7​
[/td][/tr]
[/table]

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

[Table="width:, class:head"]
[tr=bgcolor:#FFFFFF][td=bgcolor:#5B9BD5]UniqueID[/td][td=bgcolor:#5B9BD5]Value[/td][td][/td][td=bgcolor:#70AD47]UniqueID[/td][td=bgcolor:#70AD47]Value[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]42787_Bailey_a[/td][td=bgcolor:#DDEBF7]
3​
[/td][td][/td][td=bgcolor:#E2EFDA]42787_Bailey_a[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]42901_Handerson_a[/td][td]
2​
[/td][td][/td][td]42787_Bailey_a[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]42901_Handerson_b[/td][td=bgcolor:#DDEBF7]
4​
[/td][td][/td][td=bgcolor:#E2EFDA]42787_Bailey_a[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]42901_Handerson_c[/td][td]
5​
[/td][td][/td][td]42901_Handerson_a[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]42796_Atascosa_a[/td][td=bgcolor:#DDEBF7]
2​
[/td][td][/td][td=bgcolor:#E2EFDA]42901_Handerson_a[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td]42796_Atascosa_b[/td][td]
5​
[/td][td][/td][td]42901_Handerson_b[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td=bgcolor:#DDEBF7]42796_Atascosa_c[/td][td=bgcolor:#DDEBF7]
7​
[/td][td][/td][td=bgcolor:#E2EFDA]42901_Handerson_b[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42901_Handerson_b[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42901_Handerson_b[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42901_Handerson_c[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42901_Handerson_c[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42901_Handerson_c[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42901_Handerson_c[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42901_Handerson_c[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_a[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_a[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_b[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_b[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_b[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_b[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_b[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_c[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_c[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_c[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_c[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_c[/td][td]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td=bgcolor:#E2EFDA]42796_Atascosa_c[/td][td=bgcolor:#E2EFDA]
1​
[/td][/tr]

[tr=bgcolor:#FFFFFF][td][/td][td][/td][td][/td][td]42796_Atascosa_c[/td][td]
1​
[/td][/tr]
[/table]

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
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
You must log in or register to reply here.

Similar threads

smide
  • Solved
Formula for percentile if data are in multiple columns
Replies
2
Views
123
hagia_sofia
hagia_sofia
S
  • Question Question
VBA code for adding new group of columns with 3 condictions from 2 dictionary and 1 array Then fill in data from value from original columns
Replies
1
Views
127
sijpie
sijpie
N
  • Solved
Formula to consolidate Data and remove entries with no data in that year
2
Replies
10
Views
371
Newbie73
N
J
  • Locked
  • Question Question
formula for randomly choosing an option
Replies
1
Views
163
Fluff
Fluff
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
2K
CrashOD
C

Forum statistics

Threads
1,224,820
Messages
6,181,155
Members
453,021
Latest member
Justyna P

Share this page

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