Help Pulling data from one column to another

  • Thread starter Thread starter Brent Sweet
  • Start date Start date
B

Brent Sweet

Hello!

I hope I can explain this correctly. I have a column with unique
information (customer IDs) and then a column with the number of orders. I
want to create a formula to pull all customer ids into individual cells where
the orders equal 2.

For Instance

3121 1
3231 2
5121 3
5312 2
1321 3
5151 1


Results:
3231
5312

If for example 3121 makes another order and their number changes to 2, I
would like 3121 to be added to the results automatically.
 
Try this array formula** :

ID = named range refers to customer ID's
Orders = named range refers to customer orders

Assume you enter the formula in cell D2.

=IF(ROWS(D$2:D2)<=COUNTIF(Orders,2),INDEX(ID,SMALL(IF(Orders=2,ROW(ID)-MIN(ROW(ID))+1),ROWS(D$2:D2))),"")

Copy to enough cells that is at least equal to number of 2s in the Oders
range.

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)
 
Back
Top