Comparing columns and organize by the total count of the values left for List Combined and Duplicates

Tonii

New Member
Joined
Dec 3, 2020
Messages
1
Office Version
  1. 2013
Platform
  1. Windows
Hello,

I'm here today because I don't know how to change this on my own.
I would like to build 2 macros using the one here as reference one for the list combined and the other for the duplicates.

1) List combined: Compare columns, where the size of each can vary, leave the duplicates on one and reorganize the results based on the
total count of the values left.

a) Instead of only compare just two columns (1 on 1), like most requests, I'd like the option to compare any number of columns changing this.
VBA Code:
    ListARange = Cells(Rows.Count, "A").End(xlUp).Row 'find the last row with data on column A
    ListBRange = Cells(Rows.Count, "B").End(xlUp).Row 'find the last row with data on column B
This part here, if I want to compare 2 on 2 columns, this code only works properly if the longer column is A.

b) The order of organization is in the same sequence as the first(s) column(s);
Example:
If I'm comparing just two columns A and B, the sequence would be column A going down to the last cell.
If I'm comparing 2 on 2 columns (A:B with C:D), the sequence starts on A goes down to the last cell and then goes up to B and goes down
again until the last cell.

c) After that comparison, reorganize everything this way:
If the total count of the values left is an even number, put the first half on one column and second half on the adjacent;
If the total count of the values left is an odd number, put the first half + 1 one column and second half on the adjacent.


2) Duplicates: Do everything as above but for the duplicates ones.

Thank you for the help.

VBA Code:
 Sub compare()
    Dim ListA As Range
    Dim ListB As Range
    Dim c As Range

    ListARange = Cells(Rows.Count, "A").End(xlUp).Row 'find the last row with data on column A
    ListBRange = Cells(Rows.Count, "B").End(xlUp).Row 'find the last row with data on column B

    Set ListA = Range("A2:A" & ListARange) 'set your range only until the last row with data
    Set ListB = Range("B2:B" & ListBRange)
    Application.ScreenUpdating = False

    Range("C1").Value = "Files in A that are NOT in B"
    Range("D1").Value = "Files in B that are Not in A"
    Range("E1").Value = "Matching Files"

    For Each c In ListA
        If Application.CountIf(ListB, c) = 0 Then
            Cells(Cells(Rows.Count, "C").End(xlUp).Row + 1, "C").Value = c
        ElseIf Application.CountIf(ListB, c) >= 1 Then
            Cells(Cells(Rows.Count, "E").End(xlUp).Row + 1, "E").Value = c
        End If
    Next c

    For Each c In ListB
        If Application.CountIf(ListA, c) = 0 Then Cells(Cells(Rows.Count, "D").End(xlUp).Row + 1, "D").Value = c
    Next c

    Application.ScreenUpdating = True

End Sub

LC = List Combined
D = Duplicates
I put the coditioning to be easier to see.
Comparison.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMAN
1LC1on1LC2on2D1on1D2on2
2
337603722366012736641174417371533373730
4304430203161025315644164056351030393559
510401093256112932164015450321837363255
618451860391618203915157145527177602550
71741174442151723425145615572538649285
84810481543516030432145045283060402643
941154144823354811155265939454349
10818851816501811105443549414145
1171775317111654172975941553105853
12164916581329195913281860585031155358
1319919421228394122615352432143333
145245243272631527104865353563132
1550550392336452334543382091439
16555455382515442519124631472552236
17595559363355483352174434431659202
185453543335524935575649114913551522
194058402830575830753451445125010
2049424925382423820502122532622
2145434550795054552520582328
22225958585957233322
232039555545231322
249385353452040395
2536884427433650
2633554928142
272860936922
28253929
Plan1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
AF3:AJ28Cell ValueduplicatestextNO
X3:Y28Cell ValueduplicatestextNO
J22:M26,N23:N26,O22:O26,J3:N21Cell ValueduplicatestextNO
B22:F28,B3:D21Cell ValueduplicatestextNO
 

Some videos you may like

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Watch MrExcel Video

Forum statistics

Threads
1,127,612
Messages
5,625,846
Members
416,138
Latest member
Pizzaman22

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top