Match values and create a list in one cell

[Guest]

My results are to be stored in column C in Sheet 1
Sheet 2 has the data I'm searching for. I want to extract the data from
column d (always a number) on sheet 2. The Type must be "Y", Names will be an
exact match, and the category (column b) will always match at the beginning,
category on Sheet 1 has varying lengths.

Sheet 1
column a column b column c
AA Name 1 1
BBB Name 2 6,14,30

Sheet 2
column a column b column c column d
Y AA.345 Name 1 1
Y AA123 Name 2 2
Y BBB1 Name 2 30
Y BBB2 Name 2 14
N bBB23 Name 2 5
Y BBB3435 Name 2 6
Y bb12 Name 2 7

 

[Guest]

Here's a crack at this complex criteria post .. a formulas play which can
yield the required results in columnar manner in Sheet1, viz.:

In col A:

AA
Name 1
1
etc

In Col B:

BBB
Name 2
6
14
30
etc

A sample construct is available at:
http://www.savefile.com/files/465009
Multiple criteria match n extract multiple results.xls

Assume source data in Sheet2's cols A to D, from row1 down

In Sheet1,

Set it up like this:
A1:B1 contains: AA, BBB
A2:B2 contains: Name 1, Name 2

In say, D1:
=IF(AND(ISNUMBER(FIND(A$1,Sheet2!$B1)),Sheet2!$A1="Y",Sheet2!$C1=A$2),Sheet2!$D1-ROW()/10^10,"")
Copy D1 to E1, fill down to cover the max expected extent of source data in
Sheet2. Cols D and E are the criteria cols for extract into cols A and B.

The line criteria to be satisfied being:
a. col A in Sheet2 = "Y"
b. case sensitive "FIND" for "AAA" & "BBB" in col B in Sheet2
c. non-case sensitive match of "Name 1" & "Name 2" in col C in Sheet2

The result's numbers from Sheet2's col D satisfying the line criteria above
are to be extracted in descending order. And ties in these numbers, if any,
would have to be catered for (full extract).

For the extract of the multiple results' numbers, place in A3:
=IF(ROW(A1)>COUNT(D),"",INDEX(Sheet2!$D:$D,MATCH(SMALL(D,ROW(A1)),D,0)))
Copy A3 to B3, fill down to cover the same max expected extent. Cols A and B
will return the expected results, ie the numbers from Sheet2's col D which
matches the required criteria, all numbers neatly bunched at the top under
the criteria in A1:A2, B1:B2 & arranged in descending order down the col.

 

[Guest]

Oops, one errata:

The result's numbers from Sheet2's col D satisfying the line criteria above
are to be extracted in descending order.

That should read: ... in ascending order

Amend the formula in D1 slightly to:
=IF(AND(ISNUMBER(FIND(A$1,Sheet2!$B1)),Sheet2!$A1="Y",Sheet2!$C1=A$2),Sheet2!$D1+ROW()/10^10,"")

[ amend it to be: .. +ROW()/10^10, instead of previous: .. -ROW()/10^10 ]

---

 

[Guest]

Unfortunately, I can't reformat the data. It comes to me in the form I
provided.

 

[Guest]

Unfortunately, I can't reformat the data.
It comes to me in the form I provided.

But my earlier response assumed the posted source data in Sheet2 "as-is" ?
Only the outputs were in a slightly different "columnar" format.

Anyway, to get it "horizontally" laid out (& with the numeric results
concatenated in a single col C), we could try this extension to the earlier
set-up ...

A new sample is available at:
http://www.savefile.com/files/466960
Multi_criteria_match_n_extract_multi_results_2.xls

In a new Sheet1a,

In A1: =OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1)
Copy A1 to B1

In C1:
=SUBSTITUTE(TRIM(D1&" "&E1&" "&F1&" "&G1&" "&H1&" "&I1&" "&J1&" "&K1&"
"&L1&" "&M1)," ",", ")

In D1: =OFFSET(Sheet1!$A$1,COLUMN()-2,ROW()-1)
Copy D1 to M1, ie copy across to the same extent as filled down in Sheet1.
The fill down there was for 10 rows. Then select A1:M1, copy down to M2. Hide
away cols D to M, if desired. The above will return the results in the
required horizontal format within cols A to C.

If you can live with the numeric results not being concatenated in col C,
then it's much easier. Just copy the formula in A1 [ie:
=OFFSET(Sheet1!$A$1,COLUMN()-1,ROW()-1) ] right across as far as required,
then fill down to row2. This will yield the dynamic transpose of what's in
Sheet1's cols A and B, which is a very close 99.9% fit to your original specs
on the results.

---

 

[Ron Rosenfeld]

My results are to be stored in column C in Sheet 1
Sheet 2 has the data I'm searching for. I want to extract the data from
column d (always a number) on sheet 2. The Type must be "Y", Names will be an
exact match, and the category (column b) will always match at the beginning,
category on Sheet 1 has varying lengths.

Sheet 1
column a column b column c
AA Name 1 1
BBB Name 2 6,14,30

Sheet 2
column a column b column c column d
Y AA.345 Name 1 1
Y AA123 Name 2 2
Y BBB1 Name 2 30
Y BBB2 Name 2 14
N bBB23 Name 2 5
Y BBB3435 Name 2 6
Y bb12 Name 2 7

Try this:

1. Name the ranges on Sheet 2: ColA, ColB, ColC, ColD

e.g:

ColA =Sheet2!$A$2:$A$8
ColB =Sheet2!$B$2:$B$8
ColC =Sheet2!$C$2:$C$8
ColD =Sheet2!$D$2:$D$8


2. Download and install Longre's free morefunc.xll add-in from
http://xcell05.free.fr It is easily distributable with the workbook if that is
required.

3. Use this **array** formula. To enter an array formula, hold down
<ctrl><shift> while hitting <enter>. Excel will place braces {...} around the
formula:


=SUBSTITUTE(MCONCAT(VSORT((ColA="Y")*EXACT(A1,LEFT(
ColB,LEN(A1)))*(ColC=B1)*ColD,,1),", "),"0, ","")

Given your data, it gives the results you specify



--ron