Results 1 to 9 of 9

Thread: Matching cells, adding blank lines
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jul 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Matching cells, adding blank lines

    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

  2. #2
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,885
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Matching cells, adding blank lines

    So what would the result in Tab C look like, based on the posted data ??
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  3. #3
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,368
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Matching cells, adding blank lines

    Quote Originally Posted by Michael M View Post
    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.

    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 by Peter_SSs; Nov 9th, 2019 at 01:42 AM.
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  4. #4
    New Member
    Join Date
    Jul 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Matching cells, adding blank lines

    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:

    A B C I J K L
    1 Original Estimate New Estimate
    2 System Description Quantity System Description Quantity
    3 || 0100 - TEMPORARY LIGHTING & POWER TEMPORARY POWER AND LIGHTING 111,000 || 0100 - TEMPORARY LIGHTING & POWER TEMPORARY POWER AND LIGHTING 116,000
    4 || 0100 - TEMPORARY LIGHTING & POWER LIVE COUNT LINK 1 || 0100 - TEMPORARY LIGHTING & POWER TEMPORARY TRAILER CONNECTION 4
    5 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT HSB1 - 4000A 480/277V (4) section 1 || 0100 - TEMPORARY LIGHTING & POWER TEMPORARY SERVICE 3
    6 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT SLP1 - 250A 277/480V PANEL 1 || 0100 - TEMPORARY LIGHTING & POWER LIVE COUNT LINK 5
    7 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT T4 - 75KVA TRANSFORMER 1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT D2 1200A 480/277V 1
    8 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT INVERTER 1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT MDP-1 1000A 480/277V 1
    9 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 3/4" CONDUIT - EMT 16 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT HA 400A 480/277V 1
    10 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 1" CONDUIT - EMT 10 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT HB 400A 480/277V 1
    11 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 1 1/2" CONDUIT - EMT 825 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT HC 400A 480/277V 1
    12 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 2" CONDUIT - EMT 40 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT LD 100A 480/277V 1
    13 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 3" CONDUIT - EMT 20 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT LA 100A 480/277V 1
    14 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE #350 XHHW BLACK - AL 200 || 0500 - LIGHT FIXTURES & LAMPS TYPE A - LED HIGH BAY 122
    15 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE #500 XHHW BLACK - AL 690 || 0500 - LIGHT FIXTURES & LAMPS TYPE AE - LED HIGH BAY W/BATTERY PACK 45
    16 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE #8 TO #10x 7/8 PLAS ANCHOR (3/16) 1 || 0500 - LIGHT FIXTURES & LAMPS TYPE B - 2X4 LED TROFFER 79
    17 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE #10x 1 P/H SELF-TAP SCREW 1 || 0500 - LIGHT FIXTURES & LAMPS TYPE BE - 2X4 LED TROFFER W/ BATTERY PACK 14
    18 || 0500 - LIGHT FIXTURES & LAMPS TYPE A1 - 2X4 LED TROFFER 82 || 0500 - LIGHT FIXTURES & LAMPS TYPE B2 - 2X4 LED TROFFER W/DRYWALL FRAME KIT 10
    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:

    A B C I J K L
    1 Original Estimate New Estimate
    2 System Description Quantity System Description Quantity
    3 || 0100 - TEMPORARY LIGHTING & POWER TEMPORARY POWER AND LIGHTING 111,000 || 0100 - TEMPORARY LIGHTING & POWER TEMPORARY POWER AND LIGHTING 116,000
    4 || 0100 - TEMPORARY LIGHTING & POWER LIVE COUNT LINK 1 || 0100 - TEMPORARY LIGHTING & POWER TEMPORARY TRAILER CONNECTION 4
    5 || 0100 - TEMPORARY LIGHTING & POWER TEMPORARY SERVICE 3
    6 || 0100 - TEMPORARY LIGHTING & POWER LIVE COUNT LINK 5
    7 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT HSB1 - 4000A 480/277V (4) section 1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT D2 1200A 480/277V 1
    8 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT SLP1 - 250A 277/480V PANEL 1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT MDP-1 1000A 480/277V 1
    9 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT T4 - 75KVA TRANSFORMER 1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT HA 400A 480/277V 1
    10 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT INVERTER 1 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT HB 400A 480/277V 1
    11 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT HC 400A 480/277V 1
    12 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT LD 100A 480/277V 1
    13 || 0300 - LOW VOLTAGE DISTRIBUTION EQUIPMENT LA 100A 480/277V 1
    14 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 3/4" CONDUIT - EMT 16
    15 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 1" CONDUIT - EMT 10
    16 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 1 1/2" CONDUIT - EMT 825
    17 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 2" CONDUIT - EMT 40
    18 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE 3" CONDUIT - EMT 20
    19 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE #350 XHHW BLACK - AL 200
    20 || 0310 - LOW VOLTAGE FEEDER CONDUIT & WIRE #500 XHHW BLACK - AL 690
    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 SCREW 1
    23 || 0500 - LIGHT FIXTURES & LAMPS TYPE A1 - 2X4 LED TROFFER 82 || 0500 - LIGHT FIXTURES & LAMPS TYPE A - LED HIGH BAY 122
    24 || 0500 - LIGHT FIXTURES & LAMPS TYPE AE - LED HIGH BAY W/BATTERY PACK 45
    25 || 0500 - LIGHT FIXTURES & LAMPS TYPE B - 2X4 LED TROFFER 79
    26 || 0500 - LIGHT FIXTURES & LAMPS TYPE BE - 2X4 LED TROFFER W/ BATTERY PACK 14
    27 || 0500 - LIGHT FIXTURES & LAMPS TYPE B2 - 2X4 LED TROFFER W/DRYWALL FRAME KIT 10
    Comparison

    This way I can compare if I am missing anything per system when comparing
    Last edited by chethead; Nov 9th, 2019 at 02:20 PM.

  5. #5
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,368
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Matching cells, adding blank lines

    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.

    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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  6. #6
    New Member
    Join Date
    Jul 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Matching cells, adding blank lines

    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.

  7. #7
    New Member
    Join Date
    Jul 2015
    Posts
    31
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Matching cells, adding blank lines

    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

  8. #8
    MrExcel MVP
    Moderator
    Peter_SSs's Avatar
    Join Date
    May 2005
    Location
    Macksville, Australia
    Posts
    41,368
    Post Thanks / Like
    Mentioned
    100 Post(s)
    Tagged
    21 Thread(s)

    Default Re: Matching cells, adding blank lines

    Quote Originally Posted by chethead View Post
    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


    Quote Originally Posted by chethead View Post
    - 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
    Hope this helps, good luck.
    Peter
    Excel 365 - Windows 10
    - Want to help your helpers by posting a small, copyable, screen shot directly in your post? Look here
    - If posting vba code, please use Code Tags - like this [code]Paste code here[/code] - or use the # key in the Reply window
    - Read: Forum Rules, Forum Use Guidelines, & FAQ

  9. #9
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    South Western NSW
    Posts
    17,885
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Matching cells, adding blank lines

    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 ...
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •