Extract unique values from columns

[proficient]

Hi all,

Need your help, I have two columns, column A has

A B C O E W G H
whereas column B has
A B C E G H J L P

I want Unique alphabats from column B only, means as you can see O and W are also unique in column A but I want to extract J, L and P only.

TIA

 

[Aladin Akyurek]

Re: Want to extract unique values from columns

Hi,

I am using Excel 2007

RGDS,

Rizvi.M.H.

1. Assuming that the concatenation is needed...

=IFERROR(INDEX(CustomerMaster,SMALL(IF(FREQUENCY(IF(CustomerMaster<>"",IF(CodeMaster=$BL$5,MATCH(CustomerMaster&"|"&CodeMaster,CustomerMaster&"|"&CodeMaster,0))),IvecMaster),IvecMaster),ROWS(HI$7:HI7))),"")

is ok and can be speeded up by doing concatenation in a range of its own, say CusAndCode. IFERROR is costly: If needed, it can be replaced with IF(ROWS(…)>N, where N is the unique count.

2. Assuming that the unsorted unique list [1] creates does not contain numeric data...

=INDEX($HI$7:$HI$82, MATCH(LARGE(COUNTIF($HI$7:$HI$82, ">="&$HI$7:$HI$82), ROWS($HJ$7:HJ7)), COUNTIF($HI$7:$HI$82, ">="&$HI$7:$HI$82), 0))

​

3. The following

=IF(TODAY()-IF(ISERROR(LOOKUP(2,1/(CustomerMaster=HJ7),InvDtMaster)),"",(LOOKUP(2,1/(CustomerMaster=HJ7),InvDtMaster)))>120,IF(ISERROR(LOOKUP(2,1/(CustomerMaster=HJ7),InvDtMaster)),"",(LOOKUP(2,1/(CustomerMaster=HJ7),InvDtMaster))),"")​

is a bit strange. Care to describe in wordt what it is intended to do?

 

[RIZVI]

Re: Want to extract unique values from columns

The point number 3, gives me the last date of invoice. From this i can make out when was the customer lost.

 

[Aladin Akyurek]

Re: Want to extract unique values from columns

The point number 3, gives me the last date of invoice. From this i can make out when was the customer lost.

Lost if at least 120 days past?

 

[RIZVI]

Re: Want to extract unique values from columns

Lost if at least 120 days past?

Yes, I have taken as a customer is lost of he has not purchased from last 4 months, that is 120 days.

 

[Aladin Akyurek]

Re: Want to extract unique values from columns

Yes, I have taken as a customer is lost of he has not purchased from last 4 months, that is 120 days.

Does this cover the same as [3] above?

=IF(TODAY()-IFERROR(LOOKUP(2,1/(CustomerMaster=HJ7),InvDtMaster)),0))>120,
LOOKUP(2,1/(CustomerMaster=HJ7),InvDtMaster),
"")

 

[RIZVI]

Re: Want to extract unique values from columns

Yes, it does.

 

[Aladin Akyurek]

Re: Want to extract unique values from columns

Yes, it does.

That's great as it should be a tad faster than the formula it is suggested to replace.

Back to post #21 , did you also try doing the concatenation as suggested?

By the way, we cannot do much about [2] and hope that [1] does not output numbers [2] cannot chew.

 

[RIZVI]

Re: Want to extract unique values from columns

That's great as it should be a tad faster than the formula it is suggested to replace.

Back to post #21 , did you also try doing the concatenation as suggested?

By the way, we cannot do much about [2] and hope that [1] does not output numbers [2] cannot chew.

There has not been much noticeable impact on speed of computer, may be because there are other scores of such formulas slowing down the file. However this is a simpler and easier formula and I have replaced mine with your formula.

Thanks for the help.

RGDS,

Rizvi.M.H.

 

[Aladin Akyurek]

Re: Want to extract unique values from columns

There has not been much noticeable impact on speed of computer, may be because there are other scores of such formulas slowing down the file. However this is a simpler and easier formula and I have replaced mine with your formula.

Thanks for the help.

RGDS,

Rizvi.M.H.

In particular formulas with volatile functions (INDIRECT, OFFSET, etc.) do slow processing.