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 :
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.
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 | StockCode | CAT Code / Model / Yr | Stock Code | Final CAT Code | |||||
2 | NYA | AA ROC 94-97 | NYA | AA ROC 94-97, AA ROC 94-99 | |||||
3 | NYA | AA ROC 94-97 | SCFI264L | AH 500 15-, AH 595 15-17, AH 595 16-18 | |||||
4 | NYA | AA ROC 94-97 | SCFI264R | AH 500 15-, AH 595 15-17 | |||||
5 | NYA | AA ROC 94-97 | SCFI290L | AH 500 15-, AH 595 15-17 | |||||
6 | NYA | AA ROC 94-99 | SCFI290R | AH 500 15-, AH 595 15-17 | |||||
7 | NYA | AA ROC 94-99 | SCFI299L | AH124 16- | |||||
8 | NYA | AA ROC 94-99 | SCFI299R | AH124 16- | |||||
9 | SCFI300L | AH 124 16- | SCFI300L | AH124 16- | |||||
10 | SCFI300R | AH 124 16- | SCFI300R | AH124 16- | |||||
11 | SCFI299L | AH 124 16- | |||||||
12 | SCFI299R | AH 124 16- | |||||||
13 | SCFI264L | AH 500 15- | |||||||
14 | SCFI264R | AH 500 15- | |||||||
15 | SCFI290L | AH 500 15- | |||||||
16 | SCFI290R | AH 500 15- | |||||||
17 | SCFI264L | AH 500 15- | |||||||
18 | SCFI264R | AH 500 15- | |||||||
19 | SCFI290L | AH 500 15- | |||||||
20 | SCFI290R | AH 500 15- | |||||||
21 | SCFI264L | AH 595 15-17 | |||||||
22 | SCFI264R | AH 595 15-17 | |||||||
23 | SCFI290L | AH 595 15-17 | |||||||
24 | SCFI290R | AH 595 15-17 | |||||||
25 | SCFI264L | AH 595 15-17 | |||||||
26 | SCFI264R | AH 595 15-17 | |||||||
27 | SCFI264L | AH 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.