Export a list of every cell in column A:A in Sheet1 to Sheet2 if the cells in K:K on Sheet1 meet certain criteria

[jblack13]

Hello,

I have a fairly large worksheet (A1:T357), which details all of my company's sales contracts ('Contracts Data').

In this sheet, Column A contains all the contract numbers and Column K contains the codes which identify which product was sold in the contract (for example: AAAA01: Apples, BBB03: Bicycles, etc.)

I would like to create a separate worksheet for each product, and in those worksheets automatically pull a list of every contract # associated with those respective products.

In other words, I would like to create a worksheet for product AAAA01, to track all my company's Apple sales. In that worksheet, I would like a list of every contract number contained in column A:A of 'Contracts Data', provided the corresponding Column K:K reads "AAAA01" (which automatically updates when new contracts are entered with AAAA01 product code in the 'Contracts Data' sheet.

Firstly, is this possible? Secondly, is there a formulaic solution to this problem as opposed to one requiring VBA?

I suspect if it is possible it would require the combined use of INDEX(), MATCH(), ROW() and IF() statements, but frankly I'm a bit lost in the woods.

Any help would be extremely appreciated.
Thank you!

 

[jblack13]

Update:
If I isolate the 2 columns I am working with, 'Contracts Data'!K:K and 'Contracts Data'!A:A, and put them in a new test worksheet, I have found a solution that sort of works.

In other words:
If I copy and paste Column A from 'Contracts Data' (the contract numbers) and put it in Column B of new worksheet 'Contracts Test', and copy and paste Column K from 'Contracts Data' (the product codes) and put it in Column A of 'Contracts Test', the following formula works:

=IF(ISERROR(INDEX(Contracts Test!$A$1:$B$357,SMALL(IF(Contracts Test!$A$1:$A$357="AAAA01",ROW(Contracts Test!$A$1:$A$357)),ROW(1:1)),2)),"",INDEX(Contracts Test!$A$1:$B$357,SMALL(IF(Contracts Test!$A$1:$A$357="AAAA01",ROW(Contracts Test!$A$1:$A$357)),ROW(1:1)),2))

However, if I try to readjust the array ranges or move things around at all, it stops working. Admittedly, I don't fully understand all of the functionality of the above formula... specifically how it presents the next index result on the subsequent rows.

I will keep working away at it, but if someone has any insight they might like to share to help point me in the right direction I would be very grateful.
Thanks!

 

[jblack13]

Good news, I figured it out!