Adding unique items using an Array formula

  • Thread starter Thread starter SHAWN
  • Start date Start date
S

SHAWN

Guys

Could really use some help here.

In Excel spreadsheet, I have two lists, looks something like that:


List 1 List 2
124 126
126 127
127 128
129 129
130
132

Now, List 1 is displayed in the report, List 2 is a lookup table.

The report looks like this:

List 1 Status
124
126 126
127 127
129 129

Required: Doing simple vlookup, find matching values from List 2.
Since List 2 has more items then List 2, all the missing values need
to be added to the Status field after all the List 1 values have been
looked up. So, List 1 will have blanks, and Status will have
values:128,130 and 132, something like this:

List 1 Status
.... ...
129 129
128
130
132

I know this could be done thru using an Array formula, I've seen an
example some years ago, however I can't find it.

I would really like to use the standard functionality and avoid VBA.

TIA.
 
SHAWN said:
Guys
Click to expand...

In the gender-neutral sense?
In Excel spreadsheet, I have two lists, looks something like that:

List 1 List 2
124 126
126 127
127 128
129 129
130
132

Now, List 1 is displayed in the report, List 2 is a lookup table.

The report looks like this:

List 1 Status
124
126 126
127 127
129 129

Required: Doing simple vlookup, find matching values from List 2.
Since List 2 has more items then List 2, all the missing values need
to be added to the Status field after all the List 1 values have been
looked up. So, List 1 will have blanks, and Status will have
values:128,130 and 132, something like this:

List 1 Status
... ...
129 129
128
130
132

I know this could be done thru using an Array formula, I've seen an
example some years ago, however I can't find it.

I would really like to use the standard functionality and avoid VBA.
Click to expand...

Assuming you don't need help bringing List 1 into the report, just the
values in the Status column. If the 123 cell under the List 1 column in the
resport were in cell A6 on some other worksheet, and Lists 1 and 2 were
named List1 and List2, respectively, and the Status columns were in column
B, try

B2 [*array* formula]:
=IF(AND(ISNUMBER(A6),A6<MIN(List2)),"",IF(COUNTIF(List2,A6),A6,
INDEX(List2,MATCH(0,COUNTIF(B$5:B5,List2),0))))

Select B2 and fill down as needed.
 
Harlan Grove said:
B2 [*array* formula]:
=IF(AND(ISNUMBER(A6),A6<MIN(List2)),"",IF(COUNTIF(List2,A6),A6,
INDEX(List2,MATCH(0,COUNTIF(B$5:B5,List2),0))))
Click to expand...

So much for copy & paste from different ranges on my test worksheet.

Put that formula in cell B6 rather than B2.
 
Hi, Harlan

Thanks very much for your help, appreciate it.

Your formula worked perfect except for a case when Min value in List2
is less than in List1.

I couldn't get it to work. Could you please help.

Thanks
 
Harlan

Please disregard my previous post, the formula works just fine.

Thanks again, you helped me big time!!!
 
Back
Top