Expand data line by line - complicated question

[nostradamus]

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.

	A	B
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.

	A	B
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.

 

[sandy666]

You are welcome
I'm happy you are happy
Now you can click on the button Thanks/Like (bottom left corner) in the posts which have helped you
Have a nice day

 

[nostradamus]

Hi sandy666, I was going through my data and the code/query only worked for first 16k lines. I have about 64k cells that would need to be populated. Is there some limitation with this table query?
Thanks.

 

[sandy666]

there is no any limits only RAM or disc space.
maybe your data somewhere is not proper
hard to say without original file

 

[sandy666]

Excel limi: ca 1 mln rows only