Listing all row instances associated with one column value

Hello,

I have 2 worksheets. The first worksheet that lists a great number of transactions between customers and my company. Each of these transactions have a unique customer account number listed as a column value. No null values.

The second worksheet is a shorter, randomized list of these account numbers. I would like to take one of these randomized account numbers, and find all associated transactions on the first worksheet that match the account number.

There may be no transactions listed for one of the account numbers, or there could be 150. The important part is I need to list the entire ROW (approximately 20 columns for each row) for the associated transactions.

I would post a sample but the file is something like 20 mb and includes mostly sensitive information. Please let me know if my explanation doesn't make sense.

Hi and welcome to the forum

I understand that your data is sensitive, but it would help if you could put together some dummy data for us to look at and work with?

... So if the unique identifier in Column J on the first tab is present in the second tab, say, seven times, ...

So you want to search every time for a certain unique identifier ??

..again, please imagine thousands more rows than what is showing.

Perhaps if it's real that you have thousands of rows(how many?), will be better to use VBA code.

I say this, because i feel that we'll need to use an ARRAY formula for this and for thousands of rows, the calculations will be too slow...

...I was hoping for some standard formula usage. Do you think that's not possible given my situation? Or just terribly inefficient? Is speed the only advantage for using a vba function?

As i know, if you want to use standart formula, we have to use 1-2 helper-hidden columns.

This ARRAY formula gives what you need, but will be very slow with thousands of columns.

=INDEX('CA RANDOMS'!$A$2:$A$1000,SMALL(IF('CA RANDOMS'!$J$2:$J$1000=$A$1,ROW('CA RANDOMS'!$A$2:$A$1000)-1),ROW('CA RANDOMS'!A1)))

In A1 of your new sheet put your unique identifier that you want to see it's results. In A2 and copy down put my suggested formula.

One of VBA advantages is the speed. True is that i know almost nothing about VBA, so i can not helps you on this.

It's 10:30am(morning here)