Summary
I have a database of ~1500 entries. Some of them have more than one entry (up to 5) under the same identifier. I have a pre-set list of IDs I would like to check to see if they have multiple records, and if so, pull all of them, so I can compare them for differences.
Current solution
I currently have this working correctly, using a method to pull the 1st, 2nd, 3rd, 4th and 5th response respectively. The current formula is:
=INDEX('DATABASE'!$B:$B,SMALL(IF($A2='DATABASE'!$A:$A,ROW('DATABASE'!$A:$A)-ROW('DATABASE'!$A$1) 1),1)) for the 1st result, =INDEX('DATABASE'!$B:$B,SMALL(IF($A2='DATABASE'!$A:$A,ROW('DATABASE'!$A:$A)-ROW('DATABASE'!$A$1) 1),2)) for the second, and so on. In this case, the above code would check the value in A2 (ID2Check), and then do a match for it on the 'DATABASE' sheet in column A. If it matches, it will return the first value that matches from column B. The second piece of code above does the same, except it returns the second match from column B. ID2Check is populated by me, and the Results 1-5 are the code output. For example:
Database:
| ID | Data Item |
|---|---|
| AAA | Lemons |
| 111 | Greenhouse |
| FOO | Computer |
| AAA | Monitor |
| CAT | Coffee |
| ORANGE | Pintglass |
| 123 | Birthday |
| FOO | Avengers |
| AAA | Plasters |
| FOO | NachoTaco |
Code output:
| ID2Check | Result 1 | Result 2 | Result 3 | Result 4 | Result 5 |
|---|---|---|---|---|---|
| AAA | Lemons | Monitor | |||
| 123 | Birthday | ||||
| FOO | Computer | Avengers | NachoTaco |
Problem
This solution works correctly, and will pull the expected values as required. The problem is, as this code needs to be entered 5 times per identifier, and there are ~1500 records to check, I need to flash fill 7500 formulas. Unsurprisingly, this leads to Excel crashing and becoming unresponsive for the best part of 30 minutes. I am looking to find a solution that would be more efficient and stable when running en-mass. Any assistance would be appreciated.
Final Notes / Info
I'm using MSO 365, version 2202 (I cannot update beyond this). This will be run in the Desktop version of Excel. I would prefer this is done exclusively using formulas, but I am open to using Visual Basic if it would be otherwise impossible or incredibly inefficient.
CodePudding user response：
=UNIQUE(C5:C15)
=TRANSPOSE(FILTER($D$5:$D$15;$C$5:$C$15=F5))
CodePudding user response：
If you have access to HSTACK() function then could try-
=HSTACK(UNIQUE(A2:A11),TEXTSPLIT(TEXTJOIN("/",FALSE,BYROW(UNIQUE(A2:A11),LAMBDA(x,TEXTJOIN("|",TRUE,FILTER(B2:B11,A2:A11=x))))),"|","/",,,""))
