Inserting Rows Based on a Cell Value and Pasting Text from Another

**SweetBaboo** · 05-15-2020, 08:37 PM

Hello,

New to this forum. I work in healthcare.

I have an Excel issue I need to resolve. I have two columns of data. In Col A I have a list of 600 rows of unique zip codes. In Col B, I have the number of patients that live in that zip code. Example:

Zip Code Number of Pts
37362 25
37363 110
37365 14
37366 9
37367 62
37369 8
37370 14
37373 23
37374 13
37375 7
33928 1

etc...

If you sum the total of Col B, there are about 15000 patients with many living in the same zip code. I need to get all the zip codes in a single column, even if they are duplicates. I need to plot this out on a map to visualize the physical distribution of these patients.

Is there any way to do this in an efficient manner? I'm thinking of a macro but I'm not sure that's going to work.

For Col B values > 1, I'd need to add row(s) below based on the value of that cell minus one. So for Zip Code 37362, there are 25 patients. I need to insert 24 rows below and then copy the zip code from Col A in each cell below. I can do this manually but it would be very time consuming.

Any suggestions?

**Eastw00d** · 05-16-2020, 12:18 AM

Hi, you could try this macro:

Sub Insert_Rows()
    Dim i&, j&, lro&, numrows&
    lro = Cells(Rows.Count, "A").End(xlUp).Row
    i = 1
    Do Until i = lro
    numrows = Cells(i, 2).Value-1
        For j = 1 To numrows 
            Cells(i + 1, 2).EntireRow.Insert xlShiftDown
            Cells(i + 1, 1) = Cells(i, 1)
        Next j
        lro = lro + numrows 
        i = i + numrows 
        i = i + 1
    Loop
End Sub

Cheers
Erwin

**SweetBaboo** · 05-16-2020, 07:58 AM

Thanks Erwin,

I created a blank macro then pasted your code into it. When I run it, I get am error:

Run-time error '13':
Type mismatch

My VB skills are practically non existent.

**Eastw00d** · 05-16-2020, 12:16 PM

Perhaps if you copy your data into this workbook:
Another possibility is that you upload yours, without confidential data, and I will put the code in there.

Cheers
Erwin

**SweetBaboo** · 05-16-2020, 01:48 PM

Erwin,

That worked like a charm!!! Thank you so much. I really appreciate that. I have a few of these spreadsheets to do and this made my life a lot easier.

SB

**Eastw00d** · 05-16-2020, 01:53 PM

That's why we're doing this

Have a blessed day!
Cheers
Erwin

Inserting Rows Based on a Cell Value and Pasting Text from Another

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Inserting Rows Based on a Cell Value and Pasting Text from Another

Re: Inserting Rows Based on a Cell Value and Pasting Text from Another

Re: Inserting Rows Based on a Cell Value and Pasting Text from Another

Re: Inserting Rows Based on a Cell Value and Pasting Text from Another

Re: Inserting Rows Based on a Cell Value and Pasting Text from Another

Re: Inserting Rows Based on a Cell Value and Pasting Text from Another

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA Help with Pasting and Inserting Cells Based

Inserting rows based on cell

inserting rows and text based on criteria

Inserting data from one sheet to another without copy pasting an inserting extra rows

Inserting Rows Based on Cell Value.

Inserting, Cutting, and Pasting Rows Based on What's in a Cell

Inserting rows based on cell value

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions