Align two sets of data by matching Order#; then align different dates of matching #s

  • So, I want to match and align the corresponding Sales Order #s in each set of data...but then match the start date of the contract on the left with the date of its corresponding sales order in the last column. So this is like finding a matching value within a matching value.


    Here's the basic scrubbed data. For this example, I underlined the matching Sales Orders that should be aligned, and after that, the dates (in bold below) need to be aligned...which is a problem because the dates - even for the same sales order # - can be different.


    [TABLE="class: cms_table"]

    [tr]


    [TD="class: cms_table_xl70"]Sales Order #
    [/TD]
    [TD="class: cms_table_xl70"]Contract #[/TD]
    [TD="class: cms_table_xl70"]Start Date[/TD]
    [TD="class: cms_table_xl70"]End Date[/TD]
    [TD="class: cms_table_xl70, width: 88"]Sales Order #
    [/TD]
    [TD="class: cms_table_xl70, width: 111"]Sales Order Date[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl66, align: right"]6101479214[/TD]
    [TD="class: cms_table_xl66, align: right"]54027626[/TD]
    [TD="class: cms_table_xl67, align: right"]7/13/2011[/TD]
    [TD="class: cms_table_xl67, align: right"]12/18/2019[/TD]
    [TD="class: cms_table_xl68, align: right"]6101479214[/TD]
    [TD="class: cms_table_xl69, align: right"]7/13/2011[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl66, align: right"]6101485835[/TD]
    [TD="class: cms_table_xl66, align: right"]54952166[/TD]
    [TD="class: cms_table_xl67, align: right"]11/9/2011[/TD]
    [TD="class: cms_table_xl67, align: right"]12/30/2019[/TD]
    [TD="class: cms_table_xl68, align: right"]6101485835[/TD]
    [TD="class: cms_table_xl69, align: right"]11/9/2011[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl66, align: right"]6101486137[/TD]
    [TD="class: cms_table_xl66, align: right"]55073527[/TD]
    [TD="class: cms_table_xl67, align: right"]11/23/2011[/TD]
    [TD="class: cms_table_xl67, align: right"]10/29/2020[/TD]
    [TD="class: cms_table_xl68, align: right"]6101486137[/TD]
    [TD="class: cms_table_xl69, align: right"]11/23/2011
    [/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl66, align: right"]6101486679
    [/TD]
    [TD="class: cms_table_xl66, align: right"]55206820[/TD]
    [TD="class: cms_table_xl67, align: right"]12/7/2011[/TD]
    [TD="class: cms_table_xl67, align: right"]12/5/2019[/TD]
    [TD="class: cms_table_xl68, align: right"]6101486679[/TD]
    [TD="class: cms_table_xl69, align: right"]12/7/2011
    [/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl66, align: right"]6101486679
    [/TD]
    [TD="class: cms_table_xl66, align: right"]55187822[/TD]
    [TD="class: cms_table_xl67, align: right"]12/7/2011[/TD]
    [TD="class: cms_table_xl67, align: right"]12/5/2019[/TD]
    [TD="class: cms_table_xl68, align: right"]6101487377
    [/TD]
    [TD="class: cms_table_xl69, align: right"]12/6/2011
    [/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl66, align: right"]6101487377
    [/TD]
    [TD="class: cms_table_xl66, align: right"]55189203[/TD]
    [TD="class: cms_table_xl67, align: right"]12/6/2011[/TD]
    [TD="class: cms_table_xl67, align: right"]12/4/2019[/TD]
    [TD="class: cms_table_xl68, align: right"]6101487648[/TD]
    [TD="class: cms_table_xl69, align: right"]12/30/2011[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl66, align: right"]6101487377[/TD]
    [TD="class: cms_table_xl66, align: right"]55174785[/TD]
    [TD="class: cms_table_xl67, align: right"]12/5/2011[/TD]
    [TD="class: cms_table_xl67, align: right"]12/4/2019[/TD]
    [TD="class: cms_table_xl68, align: right"]6101487703[/TD]
    [TD="class: cms_table_xl69, align: right"]12/15/2011[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl66, align: right"]6101487648[/TD]
    [TD="class: cms_table_xl66, align: right"]55428788[/TD]
    [TD="class: cms_table_xl67, align: right"]12/30/2011[/TD]
    [TD="class: cms_table_xl67, align: right"]1/3/2020[/TD]
    [TD="class: cms_table_xl68, align: right"]6101488006[/TD]
    [TD="class: cms_table_xl69, align: right"]12/12/2011[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl66, align: right"]6101487703[/TD]
    [TD="class: cms_table_xl66, align: right"]55272346[/TD]
    [TD="class: cms_table_xl67, align: right"]12/15/2011[/TD]
    [TD="class: cms_table_xl67, align: right"]12/13/2019[/TD]
    [TD="class: cms_table_xl68, align: right"]6101488109[/TD]
    [TD="class: cms_table_xl69, align: right"]1/10/2012[/TD]

    [/tr]


    [/TABLE]




    So, this is as far as I've gotten. The Sales Orders are aligned as I like (underlined below), but I need to do the same with the corresponding dates (in bold) cause sometimes the dates match, sometimes they don't. Since there are multiple dates, I'm wondering if there's a way to do the True/False comparison. Normally I could do that with a simple formula. But since even the matching orders can have duplicates, it will probably be met with blank cells to it's direct left or right. So, I'm wondering if there's a way to do a more advanced kind of True/False comparison to pick apart which ones match.


    (Below, I'm using a different chunk of Sales orders than I used above to hopefully make my point clearer)


    [TABLE="class: cms_table"]

    [tr]


    [TD="class: cms_table_xl66"]Sales Order #
    [/TD]
    [TD="class: cms_table_xl66, width: 69"]Contract #[/TD]
    [TD="class: cms_table_xl66, width: 73"]Start Date[/TD]
    [TD="class: cms_table_xl66, width: 66"]End Date[/TD]
    [TD="class: cms_table_xl66, width: 88"]Sales Order #
    [/TD]
    [TD="class: cms_table_xl66, width: 111"]Sales Order Date[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl69, align: right"]6300740128
    [/TD]
    [TD="class: cms_table_xl69, align: right"]52591614[/TD]
    [TD="class: cms_table_xl70, align: right"]3/4/2013
    [/TD]
    [TD="class: cms_table_xl70, align: right"]3/3/2021[/TD]
    [TD="class: cms_table_xl67, align: right"]6300740128[/TD]
    [TD="class: cms_table_xl68, align: right"]12/10/2010[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl69"]
    [/TD]
    [TD="class: cms_table_xl69"]
    [/TD]
    [TD="class: cms_table_xl70"]
    [/TD]
    [TD="class: cms_table_xl70"]
    [/TD]
    [TD="class: cms_table_xl67, align: right"]6300740128[/TD]
    [TD="class: cms_table_xl68, align: right"]12/11/2010
    [/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl69, align: right"]6300751301[/TD]
    [TD="class: cms_table_xl69, align: right"]52747989[/TD]
    [TD="class: cms_table_xl70, align: right"]12/6/2011[/TD]
    [TD="class: cms_table_xl70, align: right"]3/13/2016[/TD]
    [TD="class: cms_table_xl67, align: right"]6300751301[/TD]
    [TD="class: cms_table_xl68, align: right"]12/30/2010[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl69, align: right"]6300753393[/TD]
    [TD="class: cms_table_xl69, align: right"]52747068[/TD]
    [TD="class: cms_table_xl70, align: right"]12/30/2010[/TD]
    [TD="class: cms_table_xl70, align: right"]5/15/2021[/TD]
    [TD="class: cms_table_xl67, align: right"]6300753393[/TD]
    [TD="class: cms_table_xl68, align: right"]12/30/2010[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl69"]
    [/TD]
    [TD="class: cms_table_xl69"]
    [/TD]
    [TD="class: cms_table_xl70"]
    [/TD]
    [TD="class: cms_table_xl70"]
    [/TD]
    [TD="class: cms_table_xl67, align: right"]6300753393[/TD]
    [TD="class: cms_table_xl68, align: right"]12/30/2010[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl69"]
    [/TD]
    [TD="class: cms_table_xl69"]
    [/TD]
    [TD="class: cms_table_xl70"]
    [/TD]
    [TD="class: cms_table_xl70"]
    [/TD]
    [TD="class: cms_table_xl67, align: right"]6300753393[/TD]
    [TD="class: cms_table_xl68, align: right"]12/30/2010[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl69, align: right"]6300759135
    [/TD]
    [TD="class: cms_table_xl69, align: right"]52827765[/TD]
    [TD="class: cms_table_xl70, align: right"]1/13/2011[/TD]
    [TD="class: cms_table_xl70, align: right"]7/28/2021[/TD]
    [TD="class: cms_table_xl67, align: right"]6300759135[/TD]
    [TD="class: cms_table_xl68, align: right"]1/13/2011[/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl69, align: right"]6300759135[/TD]
    [TD="class: cms_table_xl69, align: right"]52814196[/TD]
    [TD="class: cms_table_xl70, align: right"]1/13/2011[/TD]
    [TD="class: cms_table_xl70, align: right"]5/19/2021[/TD]
    [TD="class: cms_table_xl67"]
    [/TD]
    [TD="class: cms_table_xl68"]
    [/TD]

    [/tr]


    [tr]


    [TD="class: cms_table_xl69, align: right"]6300759408[/TD]
    [TD="class: cms_table_xl69, align: right"]52780811[/TD]
    [TD="class: cms_table_xl70, align: right"]1/7/2011[/TD]
    [TD="class: cms_table_xl70, align: right"]12/7/2019[/TD]
    [TD="class: cms_table_xl67, align: right"]6300759408[/TD]
    [TD="class: cms_table_xl68, align: right"]1/7/2011[/TD]

    [/tr]


    [/TABLE]



    If it matters, or if it's of any help, the below vba, is my source for where I'm currently at with the above example. I've used this vba in the past for other projects (but with just one match). I'm not sure if it's the right approach for what I currently need, though.


    I have also attached an excel file. This one is smaller and scrubbed; My real file has over 12,000 rows.
    forum.ozgrid.com/index.php?attachment/65410/


    https://sites.google.com/a/madrocket...ine-up-matches

  • Re: Align two sets of data by matching Order#; then align different dates of matching


    See if this is what you wanted

  • Re: Align two sets of data by matching Order#; then align different dates of matching


    It's seems to be almost exactly what I was looking for, but there seems to be a problem...


    I think once any data gets pushed outside of the originally established dimension (Dim a, i As Long, etc) then those cells no longer get looked at by the macro. But, I may be way off...


    I suspected it because at the bottom of the worksheet, after I use the macro, there is a good chunk of displaced data in columns E & F. When I first opened the attached excel file and used the macro, I thought the displaced data - since it was just in E & F - was merely Sales Order #s that had no duplicate in column A (because I deleted everything in that upload that went past row 500). But I did a Ctrl-f search of the displaced Order #s in column E and actually found matching numbers in column A. But, I did notice that the date of the displaced Order # did not match the dates of its duplicate from above. So maybe that's why it ended up there? The start dates that don't match get shifted down, and if it falls outside the original dimension, the macro no longer looks at it? Too add to that, I also noticed the formatting was stripped on those displaced cells and that started at exactly row 501 and down.

  • Re: Align two sets of data by matching Order#; then align different dates of matching


    Hmm. I'm not sure what you mean. When results of the macro are within the first 500 rows it seems to be perfect. Everything aligns the way I hoped.


    It's just that all cells pushed below row 500 (the original dim?), the macro no longer notices it. I think it's just cells, that weren't originally bound to a matching value, then get pushed below row 500, that's all. I don't know vba, but isn't it just a matter of the original range or dim being aligned with the original length of the sheet (row 500)? With the blank spaces (which I like!) cells are going to end up below row 500.


    Which, I'm guessing, is why the below formatting isn't recognized in regards to any cells below row 500.
    With .Cells(2, 5)
    myStyle = .Font.FontStyle
    myInterior = .Interior.Color
    myColor = .Font.Color

  • Re: Align two sets of data by matching Order#; then align different dates of matching


    So, I just used the macro on my original copy of the "Scrubbed Test Data xlsx" that I uploaded in my first post here; the only difference is that my uploaded xlsx is only 500 rows long because my original has exactly 12669 rows of data (and it wouldn't upload). So, I just used the macro on it. Sure enough, everything seems perfect until I reach row 12669. Then the cells lose their formatting and aren't matched or aligned with anything.

  • Re: Align two sets of data by matching Order#; then align different dates of matching


    My apologies, I'm absolutely on board with how you're trying to help. So, I took the same excel spread sheet and put in my desired results:


    The first page has untouched data, page 2 has the results with the macro (where I briefly point out examples of what's off), page 3 has my desired results (with brief description).


    Thanks in advance. :)

  • Re: Align two sets of data by matching Order#; then align different dates of matching


    Yes, well explained and thanks for the effort, but I needed to see it for solving this.
    This should do, I hope.

  • Re: Align two sets of data by matching Order#; then align different dates of matching


    Much improved with the new macro, but two problems remain; the dates in the C and F columns don't seem to align properly within a group of matching Sales Orders, and once you go past row 500 the formatting is different (dates show up as 5-digit numbers in a general format).


    Also, I noticed that when I tried to use the macro in my actual spread sheet with 12,000+ rows, I got this minor pop up that highlights this error:


    [ATTACH=CONFIG]65470[/ATTACH]


    But the good side is that no cells seems to be deleted, and the only un-matched SO#s are the ones with no duplicate.

  • Re: Align two sets of data by matching Order#; then align different dates of matching


    This is slightly different from your desired result.
    See the rows like Row 187 in your result and Row 187:188 in macro2.
    Such thing conflicts...

  • Re: Align two sets of data by matching Order#; then align different dates of matching


    Oh, wow. Yeah, I didn't even notice row 187, that was a mistake on my part. But you got it as I actually wanted it with the macro. Awesome, awesome job. Thank you!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!