Creating a list of unique items (modification needed)

[pedexe90]

Hi everyone,

I use this index function to create a list of unique items taken from list of data. The problem here is that every time I want to do this I have to manually stack up all my data and then apply this formula. I have a situation now that I have to look at two columns in two different sheets. How do I apply this function to two separate columns (not stacked up).

Here is the formula: =INDEX(B3:B13800,MATCH(0,COUNTIF($C$2:C2,B3:B13800),0))

Ideas are welcome but I would prefer not to use any macros.

Many many thanks.

 

[shaowu459]

It could be done using formulas, but it will be very much more complicated. Also you should change to another formula to get an unique list:

index(array,small(if(match(array,array,)=row(1:n),row(1:n)),row(a1)))

array in the formula could be produced using LOOKUP function.

But I still suggest use a helper column stacke them up, it is more easier and practical.

 

[Canapone]

Hi,

an attempt using more or less same formula:

first range A3:A100
second range sheet2!B3:B100

=IFERROR(IFERROR(INDEX(A$3:A$100,MATCH(0,INDEX(COUNTIF(C$2:C2,A$3:A$100),),0)),INDEX(Sheet2!B$3:B$100,MATCH(0,INDEX(COUNTIF(C$2:C2,Sheet2!B$3:B$100),),0))),"")

Regards

 

[pedexe90]

It could be done using formulas, but it will be very much more complicated. Also you should change to another formula to get an unique list:

index(array,small(if(match(array,array,)=row(1:n),row(1:n)),row(a1)))

array in the formula could be produced using LOOKUP function.

But I still suggest use a helper column stacke them up, it is more easier and practical.

Thanks for your quick reply.

Will you please expand on that please? How do I go about using a Helper Column to stack up?

 

[shaowu459]

Assuming values stored in: sheet1:C4:C10 and sheet2:A1:A8.


Enter following formula in any cell and copy down until it returns "".

=IFERROR(INDEX(CHOOSE((ROW(A1)>COUNTA(Sheet1!$C$4:$C$10))+1,Sheet1!$C$4:$C$10,Sheet2!$A$1:$A$8),IF(ROW(A1)>COUNTA(Sheet1!$C$4:$C$10),ROW(A1)-COUNTA(Sheet1!$C$4:$C$10),ROW(A1))),"")

 

[pedexe90]

Assuming values stored in: sheet1:C4:C10 and sheet2:A1:A8.


Enter following formula in any cell and copy down until it returns "".

=IFERROR(INDEX(CHOOSE((ROW(A1)>COUNTA(Sheet1!$C$4:$C$10))+1,Sheet1!$C$4:$C$10,Sheet2!$A$1:$A$8),IF(ROW(A1)>COUNTA(Sheet1!$C$4:$C$10),ROW(A1)-COUNTA(Sheet1!$C$4:$C$10),ROW(A1))),"")

Thanks.