Matching cells, adding blank lines

chethead

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
2016
Platform
Windows
Hello,

I've got an idea for a spreadsheet but am not sure where to get started. I have two tabs (Tab A and Tab B); each tab contains materials for a job. Each group of materials "A" column is the system into which the material was taken off. I would like to have all of my column A from Tab A correspond to Tab B (on a separate sheet, Tab C) but insert blank lines where I have extra items from either tab:

From Tab A From Tab B
1100 1100
1100 1100
1100
6600 6600
6600
6600
6600
7900 7900
7900 7900

I don't really think this is possible but if anyone is up for the challenge, feel free to give it a whirl.

Thank you,

Rob
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,882
Office Version
2013
Platform
Windows
So what would the result in Tab C look like, based on the posted data ??
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,688
Office Version
365
Platform
Windows
So what would the result in Tab C look like, based on the posted data ??
I have assumed that clicking 'Reply with Quote' to post 1 gives you the answer to that.


Rob,
Give this a try in a copy of your workbook.
I have assumed that 'Tab C' already exists and if there is any data in columns A:B it can be removed.

Rich (BB code):
Sub MatchValues()
  Dim dA As Object, dB As Object
  Dim a As Variant, b As Variant, itm As Variant
  Dim i As Long, r As Long
  
  Set dA = CreateObject("Scripting.Dictionary")
  Set dB = CreateObject("Scripting.Dictionary")
  With Sheets("Tab A")
    a = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(a)
    dA(a(i, 1)) = dA(a(i, 1)) + 1
  Next i
  With Sheets("Tab B")
    b = .Range("A2", .Range("A" & Rows.Count).End(xlUp)).Value
  End With
  For i = 1 To UBound(b)
    dB(b(i, 1)) = dB(b(i, 1)) + 1
  Next i
  Application.ScreenUpdating = False
  With Sheets("Tab C")
    .Columns("A:B").ClearContents
    .Range("A1:B1").Value = Array("Tab A", "Tab B")
    .Range("A2").Resize(UBound(a)).Value = a
    r = 2
    Do
      itm = .Cells(r, 1).Value
      If dB.Exists(itm) Then
        .Cells(r, 2).Resize(dB(itm)).Value = itm
        If dB(itm) > dA(itm) Then
          .Cells(r + dA(itm), 1).Resize(dB(itm) - dA(itm)).Insert Shift:=xlDown
        End If
        r = r + IIf(dB(itm) > dA(itm), dB(itm), dA(itm))
        dB.Remove itm
      Else
        r = r + dA(itm)
      End If
      dA.Remove itm
    Loop Until dA.Count = 0
    If dB.Count > 0 Then
      For Each itm In dB.Keys()
        .Cells(r, 2).Resize(dB(itm)).Value = itm
        r = r + dB(itm)
      Next itm
    End If
  End With
  Application.ScreenUpdating = True
End Sub
 
Last edited:

chethead

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
2016
Platform
Windows
ok I finally figured out the HTML maker (I hope). So to be more precise, Tab A and Tab B feed into Tab C (Comparison worksheet) in a side by side fashion. I can than visually compare if I am missing systems in either Tab A or Tab B:

ABCIJKL
1Original EstimateNew Estimate
2SystemDescriptionQuantitySystemDescriptionQuantity
3 || 0100 - TEMPORARY LIGHTING & POWERTEMPORARY POWER AND LIGHTING111,000 || 0100 - TEMPORARY LIGHTING & POWERTEMPORARY POWER AND LIGHTING116,000
4 || 0100 - TEMPORARY LIGHTING & POWERLIVE COUNT LINK1 || 0100 - TEMPORARY LIGHTING & POWERTEMPORARY TRAILER CONNECTION4
5 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTHSB1 - 4000A 480/277V (4) section1 || 0100 - TEMPORARY LIGHTING & POWERTEMPORARY SERVICE3
6 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTSLP1 - 250A 277/480V PANEL1 || 0100 - TEMPORARY LIGHTING & POWERLIVE COUNT LINK5
7 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTT4 - 75KVA TRANSFORMER1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTD2 1200A 480/277V1
8 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTINVERTER1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTMDP-1 1000A 480/277V1
9 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 3/4" CONDUIT - EMT16 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTHA 400A 480/277V1
10 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE1" CONDUIT - EMT10 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTHB 400A 480/277V1
11 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE1 1/2" CONDUIT - EMT825 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTHC 400A 480/277V1
12 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE2" CONDUIT - EMT40 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTLD 100A 480/277V1
13 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE3" CONDUIT - EMT20 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTLA 100A 480/277V1
14 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#350 XHHW BLACK - AL200 || 0500 - LIGHT FIXTURES & LAMPSTYPE A - LED HIGH BAY122
15 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#500 XHHW BLACK - AL690 || 0500 - LIGHT FIXTURES & LAMPSTYPE AE - LED HIGH BAY W/BATTERY PACK45
16 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#8 TO #10x 7/8 PLAS ANCHOR (3/16)1 || 0500 - LIGHT FIXTURES & LAMPSTYPE B - 2X4 LED TROFFER79
17 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#10x 1 P/H SELF-TAP SCREW1 || 0500 - LIGHT FIXTURES & LAMPSTYPE BE - 2X4 LED TROFFER W/ BATTERY PACK14
18 || 0500 - LIGHT FIXTURES & LAMPSTYPE A1 - 2X4 LED TROFFER82 || 0500 - LIGHT FIXTURES & LAMPSTYPE B2 - 2X4 LED TROFFER W/DRYWALL FRAME KIT 10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Comparison




But what I would like to do is match all of the systems on the left with what is one the right. I do not care what the description or quantity are or if they match; just that the systems match. Like so:

ABCIJKL
1Original EstimateNew Estimate
2SystemDescriptionQuantitySystemDescriptionQuantity
3 || 0100 - TEMPORARY LIGHTING & POWERTEMPORARY POWER AND LIGHTING111,000 || 0100 - TEMPORARY LIGHTING & POWERTEMPORARY POWER AND LIGHTING116,000
4 || 0100 - TEMPORARY LIGHTING & POWERLIVE COUNT LINK1 || 0100 - TEMPORARY LIGHTING & POWERTEMPORARY TRAILER CONNECTION4
5 || 0100 - TEMPORARY LIGHTING & POWERTEMPORARY SERVICE3
6 || 0100 - TEMPORARY LIGHTING & POWERLIVE COUNT LINK5
7 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTHSB1 - 4000A 480/277V (4) section1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTD2 1200A 480/277V1
8 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTSLP1 - 250A 277/480V PANEL1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTMDP-1 1000A 480/277V1
9 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTT4 - 75KVA TRANSFORMER1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTHA 400A 480/277V1
10 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTINVERTER1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTHB 400A 480/277V1
11 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTHC 400A 480/277V1
12 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTLD 100A 480/277V1
13 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENTLA 100A 480/277V1
14 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 3/4" CONDUIT - EMT16
15 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE1" CONDUIT - EMT10
16 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE1 1/2" CONDUIT - EMT825
17 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE2" CONDUIT - EMT40
18 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE3" CONDUIT - EMT20
19 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#350 XHHW BLACK - AL200
20 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#500 XHHW BLACK - AL690
21 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#8 TO #10x 7/8 PLAS ANCHOR (3/16)1
22 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE#10x 1 P/H SELF-TAP SCREW1
23 || 0500 - LIGHT FIXTURES & LAMPSTYPE A1 - 2X4 LED TROFFER82 || 0500 - LIGHT FIXTURES & LAMPSTYPE A - LED HIGH BAY122
24 || 0500 - LIGHT FIXTURES & LAMPSTYPE AE - LED HIGH BAY W/BATTERY PACK45
25 || 0500 - LIGHT FIXTURES & LAMPSTYPE B - 2X4 LED TROFFER79
26 || 0500 - LIGHT FIXTURES & LAMPSTYPE BE - 2X4 LED TROFFER W/ BATTERY PACK14
27 || 0500 - LIGHT FIXTURES & LAMPSTYPE B2 - 2X4 LED TROFFER W/DRYWALL FRAME KIT 10

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Comparison

This way I can compare if I am missing anything per system when comparing
 
Last edited:

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,688
Office Version
365
Platform
Windows
This time I have assumed that 'Tab C' already exists and if there is any data in columns A:C and J:L it can be removed.
Not highly tested, but give this a go.

Rich (BB code):
Sub MatchValues_v2()
  Dim dA As Object, dB As Object
  Dim a As Variant, b As Variant, itm As Variant, Headings As Variant
  Dim i As Long, r As Long
  Dim Brng As Range
  
  Set dA = CreateObject("Scripting.Dictionary")
  Set dB = CreateObject("Scripting.Dictionary")
  With Sheets("Tab A")
    a = .Range("A2", .Range("C" & Rows.Count).End(xlUp)).Value
    Headings = .Range("A1:C1").Value
  End With
  For i = 1 To UBound(a)
    dA(a(i, 1)) = dA(a(i, 1)) + 1
  Next i
  With Sheets("Tab B")
    Set Brng = .Range("A1", .Range("C" & Rows.Count).End(xlUp))
    b = Brng.Value
  End With
  For i = 2 To UBound(b)
    dB(b(i, 1)) = dB(b(i, 1)) + 1
  Next i
  Application.ScreenUpdating = False
  With Sheets("Tab C")
    .Range("A:C, J:L").ClearContents
    .Range("A1:C1").Value = Headings
    .Range("J1:L1").Value = Headings
    .Range("A2").Resize(UBound(a), UBound(a, 2)).Value = a
    r = 2
    Do
      itm = .Cells(r, 1).Value
      If dB.Exists(itm) Then
        Brng.AutoFilter Field:=1, Criteria1:=itm
        Brng.Offset(1).Copy Destination:=.Cells(r, "J")
        If dB(itm) > dA(itm) Then .Cells(r + dA(itm), 1).Resize(dB(itm) - dA(itm)).Insert Shift:=xlDown
        r = r + IIf(dB(itm) > dA(itm), dB(itm), dA(itm))
        dB.Remove itm
      Else
        r = r + dA(itm)
      End If
      dA.Remove itm
    Loop Until dA.Count = 0
    If dB.Count > 0 Then
      For Each itm In dB.Keys()
        Brng.AutoFilter Field:=1, Criteria1:=itm
        Brng.Offset(1).Copy Destination:=.Cells(r, "J")
        r = r + dB(itm)
      Next itm
    End If
  End With
  Brng.AutoFilter Field:=1
  Application.ScreenUpdating = True
End Sub
 

chethead

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
2016
Platform
Windows
It doesn't look like my reply posted yesterday. Thank you for your help Peter but unfortunately this does not seem to work. I would tweek it if I only knew what I was trying to tweek. I have not learned VBA yet.
 

chethead

New Member
Joined
Jul 23, 2015
Messages
43
Office Version
2016
Platform
Windows
I was going to start a new thread, but maybe you are willing to take one more crack at it.

I already have the worksheet that fills in from my feeder worksheets. It looks like the first HTML I have posted with the following exceptions:

- The worksheet name is Comparison
- Cells A1:E1, G1:H1: and J1:N1 are merged and filled with heading type information
- Cells in row 2 are all sub heading type information
- Cells in rows 3 through 2500 are being pulled from feeder sheets

My new thought is to leave everything as is - I populate the two feeder worksheets that feed into the "Comparison" worksheet. I will then add a button to sort the items on the Comparison worksheet. The button will add blank partial rows of cells (either A:E or J:N) where the information isn't the same between column A and column J. The result would then look like the second HTML I posted. All information would need to remain though; just shifted down so columns A:J match.

If you are willing to give it one more go, I would appreciate it. Otherwise, I might through it back out to the community.

Thank you,

Rob
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
44,688
Office Version
365
Platform
Windows
I was going to start a new thread, ...
Best not to do that - refer to number 12 of the Forum Rules and number 6 of the Forum Use Guidelines. :)


.. unfortunately this does not seem to work.
That doesn't give us much to go on. ;)
In what way does it not work?
- Does nothing?
- Crashes Excel?
- Puts the right answer in the wrong place
- Puts the wrong answer (examples) in the right place
- etc


I think much of the problem is likely that I didn't have my test sheets set up quite right but some more clarification is needed in that regard.
See comments/questions in red below


- The worksheet name is Comparison OK, you really told us that before but I missed it

Which Sheet(s) do the following apply to?
- Cells A1:E1, G1:H1: and J1:N1 are merged and filled with heading type information
- Cells in row 2 are all sub heading type information
- Cells in rows 3 through 2500 are being pulled from feeder sheets
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
18,882
Office Version
2013
Platform
Windows
I have assumed that clicking 'Reply with Quote' to post 1 gives you the answer to that.
Yeah, Looked at that but still wasn't certain what the OP was looking for ...:LOL:
 

Watch MrExcel Video

Forum statistics

Threads
1,099,364
Messages
5,468,189
Members
406,570
Latest member
Ktvu2006

This Week's Hot Topics

Top