• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compare Cols & Count Unique Values - Paste To Other Sheet

Logit

Active Member
I trust the following explanation will be understood. I tried to provide only the basic information required for understanding (no fluff) and the visual
depiction of the data involved.
-----------------------------------
Columns A & B represent a small segment of tens of thousands of rows of similar data.

Goal is to count how many entries match the data in Col A & Col B.

In other words :

NYA & AA ROC 94-97 can be found 4 times in the two columns.
NYA & AA ROC 94-99 can be found 3 times in the two columns.

94-97 is found more times than 94-99.

We want to paste NYA in Col H, then the two designations from
Col B in Col I, separated by a comma.

94-97 is listed first because the count of these is higher than 94-99.


Going to :

SCF1264L & AH 500 15- ... there are two of those in the two columns.
SCF1264L & AH 595 15-17 .. there are two of those in the two columns.
SCF1264L & AH 595 16-18 .. there is only one of those in the two columns.

We paste SCF1264L in Col H and the three CAT Codes in the occurence order listed above (2, 2, 1)

Overall goal: Search Col A for unique values and the adjacent value in Col B, beginning with row 2.
Parse the findings as outlined above to Col H & Col I as shown.

Move down Col A to the next unique value. Repeat the process.

Continue down Col A for all unique values, performing the same process.


PLEASE NOTE :

There will be tens of thousands of rows of this type data.

Col I can only have the top three CAT Codes due to the planned width of Col I. (i.e., Top highest count, second highest count, third highest count)

The values in Col A & Col B will actually be on Sheet1 in Cols AE & AG respectively.
The values in Col H & I will actually be on Sheet2 in Cols A & B


Sheet layout for review :


A
B
C
D
E
F
G
H
I
1
StockCodeCAT Code / Model / YrStock CodeFinal CAT Code
2
NYAAA ROC 94-97NYAAA ROC 94-97, AA ROC 94-99
3
NYAAA ROC 94-97SCFI264LAH 500 15-, AH 595 15-17, AH 595 16-18
4
NYAAA ROC 94-97SCFI264RAH 500 15-, AH 595 15-17
5
NYAAA ROC 94-97SCFI290LAH 500 15-, AH 595 15-17
6
NYAAA ROC 94-99SCFI290RAH 500 15-, AH 595 15-17
7
NYAAA ROC 94-99SCFI299LAH124 16-
8
NYAAA ROC 94-99SCFI299RAH124 16-
9
SCFI300LAH 124 16-SCFI300LAH124 16-
10
SCFI300RAH 124 16-SCFI300RAH124 16-
11
SCFI299LAH 124 16-
12
SCFI299RAH 124 16-
13
SCFI264LAH 500 15-
14
SCFI264RAH 500 15-
15
SCFI290LAH 500 15-
16
SCFI290RAH 500 15-
17
SCFI264LAH 500 15-
18
SCFI264RAH 500 15-
19
SCFI290LAH 500 15-
20
SCFI290RAH 500 15-
21
SCFI264LAH 595 15-17
22
SCFI264RAH 595 15-17
23
SCFI290LAH 595 15-17
24
SCFI290RAH 595 15-17
25
SCFI264LAH 595 15-17
26
SCFI264RAH 595 15-17
27
SCFI264LAH 595 16-18

I've tried several different times to create a macro to accomplish this goal. Each time resulted in grey matter melt down. Any assistance is greatly appreciated.
 

Attachments

  • Book1.xlsx
    8.7 KB · Views: 2
Nebu

Thank you for your response and the excellent solution. I am very grateful for your solution.
 
Back
Top