Insert a row based on Condition using macro

June 4, 2018 at 01:35:19
Specs: Windows 10
Hi, I have this excel sheet. I want to check from cells B66:B90, which contains names. If the first value in B66 matches the first value in B100, then do nothing else, insert a row at B100 and add that name there. Same for the entire range from B66:B90.
I hope someone can help me with this macro.

message edited by aashi95


See More: Insert a row based on Condition using macro

Reply ↓  Report •

#1
June 4, 2018 at 06:07:46
I am confused by so many things...

If the first value in B66 matches the first value in B100...

What do you mean by "first value"? Are there multiple values in those cells?

...then do nothing else, insert a row at B100 and add that name there

What do you mean by "do nothing else"? Inserting rows and adding names is not "doing nothing".

What do you mean by at B100. Above B100 or below B100?

Same for the entire range from B66:B90.

Are you saying that B66 should be compared to B100, then B67 should be compared to B101, then B68 should be compared to B102, etc?

If so, I assume that you realize that if we insert a Row at B100, then the data in B101 will now be in B102, so B67 will have to be compared to B102 not B101, and so on. Each time a Row is inserted all the data below is going to be shifted down by on Row, changing which cells need to be compared.

Is that what you are expecting to happen?

message edited by DerbyDad03


Reply ↓  Report •

#2
June 4, 2018 at 09:04:54
Yes this is what i mean. If the value at B66 does not match value at B100, i want to insert a row at B100 and shift everything down.
I then want to copy B66 to the inserted row.
Now i want to compare B67 to the next row after the inserted row and so on till B90.

Reply ↓  Report •

#3
June 4, 2018 at 09:06:15
If the value in the cells match, then i want to do nothing and just move to the next cell.

Reply ↓  Report •

Related Solutions

#4
June 4, 2018 at 10:51:07
So you left out "if not" in this statement:

If the first value in B66 matches the first value in B100, then do nothing else, insert a row at B100 and add that name there.

If the first value in B66 matches the first value in B100, then do nothing else, if not, insert a row at B100 and add that name there.

And the word "first" in "first value" was not needed.

OK, I'll work on something

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#5
June 4, 2018 at 11:11:36
Okay, thanks a lot! Hope to get a solution to this.

Reply ↓  Report •

#6
June 4, 2018 at 11:13:48
Try this:

Sub InsertMissingValues()
Dim srcRw As Long
Dim dstRw As Long

'Initialize Destination Row variable
  dstRw = 99
  
'Loop through B66:B90
    For srcRw = 66 To 90
   
'Increment dstRw
      dstRw = dstRw + 1
    
'Compare Source Value to Destination Value
'Insert Source if not found
        If Cells(srcRw, 2) <> Cells(dstRw, 2) Then
           Cells(srcRw, 2).Copy
           Cells(dstRw, 2).Insert shift:=xlDown
        End If
    Next

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#7
June 4, 2018 at 21:16:43
Hi, this is getting copied twice.
Also, I want the entire row to shift down, as there are values in columns C,D, etc., so everything should shift down, not just B column, i.e., the entire row.
Another thing is, if there is already a value at B100, but its not matching at B66, it should skip that and not delete that row at B100.
Thanks.

Reply ↓  Report •

#8
June 4, 2018 at 21:25:50

Hi, this is getting copied twice.

What "this" is getting copied twice?

In my testing, nothing gets copied twice and nothing gets deleted.

There is not much more that I can do unless you post some example data for me to work with.

I'd need to see a "before" set of example data and the desired output based on the input.

Please click on the following How To link before posting your example data.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#9
June 4, 2018 at 22:14:27
This is the data from B66 onwards:
Let this be A:
 
Colin Chilvers 
 Natalie Binks 

Jayne Duerden
 Aidan Pimm 
 Andrew Johnson 
 Andrew Pugh 

I want to copy the data here at B100:
Let this be B:

Colin Chilvers 

Jayne Duerden 
 Elizabeth Morris 
 Andrew Johnson 
 Andrew Pugh 

As Natalie is not there in B, it should insert a row after Colin in B and add Natalie.
Now Jayne Duerden is there in B, so it should just be as it is.
Similarly, Aidan Pimm will be copied from A to B, by inserting a row after Jayne.
As Elizabeth Morris is not there in A but there in B, it should leave that row in B as it is.
And so on for the rest of the rows of A.


Reply ↓  Report •

#10
June 4, 2018 at 22:17:05
The after desired output should be like this:
Colin Chilvers 
 Natalie Binks 

Jayne Duerden
Aidan Pimm
Elizabeth Morris
Andrew Johnson 
Andrew Pugh 


Reply ↓  Report •

#11
June 5, 2018 at 03:20:45
I am seeing indentations and blank rows. Is there some significance to this format?

In #10, some of the indentations are no longer there. Once again, confusion.

BTW...I just want to give you a fair warning. I'm not 100% sure that this can be done. Keeping track of each list and inserting names at specifics location may be quite difficult to accomplish. I may get something that works for that example data set but I can't be sure that is will scale for the entire range.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#12
June 5, 2018 at 04:13:21
Yes, actually the indentations are because the data is from a pivot table.
But you can ignore the indentations as of now.
But yes, there are blanks in the pivot table.

Okay, maybe if it works for this data set, i might be able to try for the entire range.
Thanks.


Reply ↓  Report •

#13
June 5, 2018 at 05:57:27
I'll see what I can do, but I'll be traveling over the next few days and my "play time" will be limited. If you don't hear anything it's not because I've given up.

I'll let you know either way.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#14
June 5, 2018 at 08:14:28
Ohh thank you so much :)
Have a safe journey

Reply ↓  Report •

#15
June 5, 2018 at 12:20:21
The requirements you have laid out can not be met, at least not as stated. Let me explain the problem and then ask some questions that may clear things up.

This is what you asked for:

If the value at B66 does not match value at B100, i want to insert a row at B100 and shift everything down.
I then want to copy B66 to the inserted row.
Now i want to compare B67 to the next row after the inserted row and so on till B90.

The problem with these instructions is that they do not account for the fact that you have names in the B100 data set that do not appear in the B66 data set.

Let's look at your example data:

66       Colin Chilvers 
67       Natalie Binks 
68
69       Jayne Duerden
70       Aidan Pimm 
71       Andrew Johnson 
72       Andrew Pugh 



100    Colin Chilvers 
101
102    Jayne Duerden 
103    Elizabeth Morris 
104    Andrew Johnson 
105    Andrew Pugh 

Let's see what happens as the code runs through it's loop. The first 5 loops are going to work fine.

B66 and B100 both contain Colin Chilvers so nothing will change.
B67 contains Natalie Binks but B101 is blank, so Natalie Binks (entire row) will be inserted at Row 101
B68 and B102 (the next row after the inserted row) are both Blank so nothing will happen
B69 and B103 both contain Jayne Duerden so nothing will change.
B70 contains Aidan Pimm but B104 contains Elizabeth Morris so Aidan Pimm (entire row) will be inserted at Row 104.

Now, the problems begin:

B71 contains Andrew Johnson but B105 (the next row after the inserted row) contains Elizabeth Morris so Andrew Johnson (entire row) will be inserted at Row 105. That's not correct because Andrew Johnson already appears in B106.

After this, it's all going to go wrong because none of the remaining names in B72:B90 are going to match anything if the code continues checking cell-by-cell. It's all because Elizabeth Morris does not appear in the B66:B90 list and therefore throws the cell-by-cell sequence out of wack.

Therefore, the cell-by-cell comparison will not work as long as there are names in the B100 range that do not appear in the B66:B90 range.

Now let me ask you about the blank rows. Do the blank rows have any significance as far as a "grouping"? In other words, does Natalie belong to the "Colin" group while Aidan, Andrew J and Andrew P belong to the "Jayne" group? To put it another way, can I use the first blank row (B68) as a "delimiter" and then check to see if all names in the "Colin" group (B66:B68) appear with Colin in the B100:B101 range and then use the next blank row (B73) to check for all names associated with Jayne (B69:B73) in the "shifted" Jayne group in the lower data set?

Does those questions make sense?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#16
June 5, 2018 at 19:55:08
Yes you are right. These blank rows are like a delimiter, to group them.
Yes you could do that. Also, you can insert an additional row anywhere in that particular group. It should just be under that specific person.

Reply ↓  Report •

#17
June 6, 2018 at 12:22:33
OK, try this code.

Note: The blank cell after each group in B66:B90 is required. That blank cell is used to determine the end of each group.

I used the terms "Manager" and "Worker" just as a way to differentiate the first name in a group (Manager) from the names below (Worker)

Let me know what you think, but my trip starts tonight so my time to fix any issues will be sporadic.

Sub BuildRange()

'Initialze Row and Column variables
  startRw = 66
  endRw = 65

'Find Range of Group
nxtGrp:
  For grpRw = startRw To 90
     If Cells(grpRw, 2) <> "" Then
       endRw = endRw + 1
     Else: Exit For
     End If
  Next
  
'Continue if valid Group was found
 If endRw > startRw Then
 
'Build Group address string
 grpRange = Range(Cells(startRw, 2), Cells(endRw, 2)).Address

'Get manager of Group
   mgr = Cells(startRw, 2)

'Find Manager in B100 range
 With Range(Cells(100, 2), Cells(Cells(Rows.Count, 2).End(xlUp).Row, 2))
  Set m = .Find(mgr, lookat:=xlPart)

'Loop through upper Group, searching for names in B100 Range
  For Each cell In Range(grpRange)
    Set wkr = .Find(cell, lookat:=xlPart)
     
'If worker is not found in B100 range, Insert it after Manager name
     If wkr Is Nothing Then
        cell.EntireRow.Copy
        Cells(m.Row + 1, 1).Insert shift:=xlDown
     End If
  Next
 End With

'Increment endRw and startRw variables and build next Group Address
    endRw = endRw + 1
    startRw = endRw + 1
  GoTo nxtGrp
 End If
 
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#18
June 6, 2018 at 23:35:18
OK, I tried this code, and its not working properly.

1. We would also need to check if the manager is there or not. If its not there, we would need to add the manager as well, which could e inserted at the end.

2. The name at B67 itself, ie., Natalie Binks, is not getting inserted.

3. Also, if a row has to be inserted, i just want a blank row to be inserted and just the name from the pivot table to be copied into column B. The rest of the columns of that row should not be copied, and should be blank.

4. Also, there is a situation, where a person's name could be under both, "manager" as well as "Worker", so its creating a problem while copying over there.

eg.

73   Ben Carter
74      Brett Johnson
75
76   Brett Johnson
77      Andrew Reeves
   

So in this case what is happening is that, Andrew Reeves, is getting copied under Ben Carter.


Reply ↓  Report •

#19
June 11, 2018 at 11:12:49
I think I'll be moving on now.

I spent a considerable amount of time writing code based on your written requirements and example data. Now that you have changed the requirements and provided a different data set, all of my past work is useless. Considering the time that I wasted meeting your original requirements, I just don't feel like starting from scratch.

re: OK, I tried this code, and its not working properly.

Actually, the code is working perfectly based on your original requirements and the example data set that you provided. Test it against the data set that you provided and you'll see that.

In Response #7 you said:

...everything should shift down, not just B column, i.e., the entire row.

In Response #18 you said: "The rest of the columns of that row should not be copied, and should be blank."

everything shifting down is not the same thing as the rest of the row should be blank.

In response #18 you also said:

"There is a situation, where a person's name could be under both, "manager" as well as "Worker", so its creating a problem while copying over there."

The problem that was created is that you did not include that fact with your original example data set. That situation changes the task immensely and results in the need to toss just about everything that was written prior. The entire "grouping" scheme and "find" methodology will not work with your most recent example data.

If I may, I would like to suggest that in the future you review your requirements before submitting them and that you ensure that you have included correct and complete information. If this was a "paid service" there would certainly be a charge for changing requirements once the work had begun. The same due diligence should be exercised when the services are provided for free.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

Ask Question