Dynamically Add Rows and Populate with Anchored Values

zahidavi

New Member
Joined
Jul 24, 2013
Messages
4
Hi Folks - long time listener, first time caller.

Problem Summary: I need to dynamically add rows below an cell and populate those cells with unique values found in the anchor row.
Contextual Detail: Data set is below but each unique entity has a document associated with it and additional documents also associated with it. Each entity, document, and related document all of unique IDs. I need to create a view which has entity ID, entity name, and unique document IDs on each row/record.
Specs: I am using MS Excel 2013.


Raw Data Set:

Entity IDEntity NameDocument Serial NumberRelated Document Serial NumberTotal Number of Documents
5456Alpha9475,6624,868,782,7205
7656Braco55394682,66243735703
4952Charlie5548,46822

<tbody>
</tbody>








End Goal:


Entity IDEntity NameDocument Serial Number
5456Alpha9475
5456Alpha6624
5456Alpha868
5456Alpha782
5456Alpha720
7656Bravo5539
7656Bravo4682
7656Bravo6624373570
4952Charlie5548
4952Charlie4682

<tbody>
</tbody>


















Highly appreciate any and all comments. Thanks!
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

I'm assuming that the two document serial number columns are formatted as text, else Excel could interpret some of those comma-delimited serial numbers as numbers, inserting or moving commas.
I have also assumed that every row will have at least one document number in the first document serial number column.

Test this in a copy of your workbook.

Rich (BB code):
Sub zahidavi_Reorg()
  Dim a, b, Docs
  Dim i As Long, j As Long, k As Long
  
  a = Range("A1").CurrentRegion.Value
  ReDim b(1 To Application.Sum(Application.Index(a, 0, 5)), 1 To 3)
  For i = 2 To UBound(a)
    Docs = Split(a(i, 3) & "," & a(i, 4), ",")
    For j = 1 To a(i, 5)
      k = k + 1
      b(k, 1) = a(i, 1): b(k, 2) = a(i, 2): b(k, 3) = Docs(j - 1)
    Next j
  Next i
  With Range("A1:C1")
    .Offset(UBound(a) + 2).Value = .Value
    .Offset(UBound(a) + 3).Resize(k).Value = b
  End With
End Sub


Sample data in rows 1:4 below, results produced by code in rows 7:17

Excel Workbook
ABCDE
1Entity IDEntity NameDocument Serial NumberRelated Document Serial NumberTotal Number of Documents
25456Alpha9475,6624,868,782,7205
37656Braco55394682,66243735703
44952Charlie5548,46822
5
6
7Entity IDEntity NameDocument Serial Number
85456Alpha9475
95456Alpha6624
105456Alpha868
115456Alpha782
125456Alpha720
137656Braco5539
147656Braco4682
157656Braco6624373570
164952Charlie5548
174952Charlie4682
18
Reorg
 

zahidavi

New Member
Joined
Jul 24, 2013
Messages
4
@Peter_SSs

Thank you Sir. This works perfectly until I get to a record which has a value of 48 in Column E (Total Number of Documents). Any way that can be fixed?
Thanks again for your help.
 

zahidavi

New Member
Joined
Jul 24, 2013
Messages
4
The specific error: "Run=time error '13': Type mismatch"
After pressing the Debug button, it leads me to the ReDim b(1 To Application.Sum(Application.Index(a, 0, 5)), 1 To 3) line.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

The number 48 should have nothing to do with the problem. I have successfully processed some sample data with 48 (and larger) values in column E.

How many rows (approximately) of data does your sheet contain?

Does column E contain formulas?

Are you sure there are no error values in column E?
 
Last edited:

zahidavi

New Member
Joined
Jul 24, 2013
Messages
4
The number 48 should have nothing to do with the problem. I have successfully processed some sample data with 48 (and larger) values in column E.

How many rows (approximately) of data does your sheet contain?

Does column E contain formulas?

Are you sure there are no error values in column E?

The sheet contains about 700 rows of data.
E strictly contains values.
I am sure E doesn't contain any errors to the extent that I've checked that there are no extra commas, spaces, and only contains numbers.
What's interesting is that if i shorten the last value in cell D from 5 digits (XXXXX) to 3 digits (XXX) it runs perfectly fine. Not sure if that's indicative of anything.

Thanks again Peter.
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
The sheet contains about 700 rows of data.
E strictly contains values.
I am sure E doesn't contain any errors to the extent that I've checked that there are no extra commas, spaces, and only contains numbers.
What's interesting is that if i shorten the last value in cell D from 5 digits (XXXXX) to 3 digits (XXX) it runs perfectly fine. Not sure if that's indicative of anything.

Thanks again Peter.
1. In a copy of your workbook, if you leave that last value in D as 5 digits so the error happens, and delete most of the middle rows of data (leaving say 10 rows but still with the problem value at the end) and run the code on that small data, does the error still occur? If so, can you post that small data set so I can see if I can replicate the problem?

2. What is the sum of all the values in column E in the original 700 row data? If you click on the 'E' column label to select the whole column the sum should be displayed on the status bar at the bottom right of your window.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,514
Messages
5,602,091
Members
414,501
Latest member
mdhaumyu

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
Top