• 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

Logit

Active Member
Nebu

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