Hi
Need some help on below:-

I will like the results to return as below based on raw data in spreadsheet
2..
how can i achieve this? the formulas i'm using returns the duplicate IDs in
rows if it appears more than once in spreadsheet 2

Using the formula, "INDEX('Closed To Date'!$A$1:$J$9926,SMALL(IF('Closed To
Date'!$A$1:$A$9926=$C$4,ROW('Closed To Date'!$A$1:$A$9926)),ROW(3:3)),10)"
the results return
ID Lic Mnt
1234 $10 $25
1234 $10 $25
4567 - $50

Desired results:-
Spreadsheet 1: Owner = ABC
Return the results only for owner ABC in below format:-
ID Lic Mnt
1234 $10 $25
4567 - $50

Spreadsheet 2:
Owner Values ID Product
ABC $10 1234 Lic
ABC $50 4567 Mnt
EFD $60 4569 Mnt
EFD $50 7893 mnt
GCH $30 2456 Mnt
GEH $20 5647 mnt
EFH $35 2434 mnt
ABC $25 1234 Mnt

Thanks, any help is appreciated!
--
nikko

Hi,

yo may want to refer to Question 8 of the following link -
http://ashishmathur.com/knowledgebaseII.aspx

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"nikko" wrote in message
...
Hi
Need some help on below:-

I will like the results to return as below based on raw data in
spreadsheet
2..
how can i achieve this? the formulas i'm using returns the duplicate IDs
in
rows if it appears more than once in spreadsheet 2

Using the formula, "INDEX('Closed To Date'!$A$1:$J$9926,SMALL(IF('Closed
To
Date'!$A$1:$A$9926=$C$4,ROW('Closed To Date'!$A$1:$A$9926)),ROW(3:3)),10)"
the results return
ID Lic Mnt
1234 $10 $25
1234 $10 $25
4567 - $50

Desired results:-
Spreadsheet 1: Owner = ABC
Return the results only for owner ABC in below format:-
ID Lic Mnt
1234 $10 $25
4567 - $50

Spreadsheet 2:
Owner Values ID Product
ABC $10 1234 Lic
ABC $50 4567 Mnt
EFD $60 4569 Mnt
EFD $50 7893 mnt
GCH $30 2456 Mnt
GEH $20 5647 mnt
EFH $35 2434 mnt
ABC $25 1234 Mnt

Thanks, any help is appreciated!
--
nikko

nikko wrote...
....
Desired results:-
Spreadsheet 1: Owner = ABC
Return the results only for owner ABC in below format:-
ID Lic Mnt
1234 $10 $25 *
4567 - $50 * * *

Spreadsheet 2:
Owner Values ID Product
ABC $10 1234 Lic
ABC $50 4567 Mnt
EFD $60 4569 Mnt
EFD $50 7893 mnt
GCH $30 2456 Mnt
GEH $20 5647 mnt
EFH $35 2434 mnt
ABC $25 1234 Mnt
....

If the table in Spreadsheet 2 (including the row of column headings)
were named Tbl, and if the results began in cell A1 in Spreadsheet 1,

A1:
Owner

B1:
ABC

A3:
ID

B3:
Lic

C3:
Mnt

A4:
=VLOOKUP($B$1,Tbl,3,0)

B4 [array formula]:
=IF(COUNT(1/(INDEX(Tbl,0,3)=$A4)/(INDEX(Tbl,0,4)=B$3)),
INDEX(Tbl,MATCH(1,(INDEX(Tbl,0,3)=$A4)*(INDEX(Tbl, 0,4)=B$3),0),2),"-")

Fill B4 right into C4.

A5 [array formula]:
=INDEX(Tbl,MATCH(1,(INDEX(Tbl,0,1)=$B$1)
*(COUNTIF(A$4:A4,INDEX(Tbl,0,3))=0),0),3)

Fill B4:C4 down into B5:C5, then fill A5:C5 down as far as needed.

Note: these formulas recalculate V E R Y S L O W L Y !

You may be better off using a pivot table for this.

Hi,
when you do a vlookup - =VLOOKUP($B$1,Tbl,3,0) ; it only returns the very
1st id.
But under owner ABC, i have few Ids to return...

there are 3 lines in the source data for owner ABC
but i only want it to return 2 lines, as one of the id - 1234 is duplicate
(appearing twice)....

--
nikko


"Harlan Grove" wrote:

nikko wrote...
....
Desired results:-
Spreadsheet 1: Owner = ABC
Return the results only for owner ABC in below format:-
ID Lic Mnt
1234 $10 $25
4567 - $50

Spreadsheet 2:
Owner Values ID Product
ABC $10 1234 Lic
ABC $50 4567 Mnt
EFD $60 4569 Mnt
EFD $50 7893 mnt
GCH $30 2456 Mnt
GEH $20 5647 mnt
EFH $35 2434 mnt
ABC $25 1234 Mnt
....

If the table in Spreadsheet 2 (including the row of column headings)
were named Tbl, and if the results began in cell A1 in Spreadsheet 1,

A1:
Owner

B1:
ABC

A3:
ID

B3:
Lic

C3:
Mnt

A4:
=VLOOKUP($B$1,Tbl,3,0)

B4 [array formula]:
=IF(COUNT(1/(INDEX(Tbl,0,3)=$A4)/(INDEX(Tbl,0,4)=B$3)),
INDEX(Tbl,MATCH(1,(INDEX(Tbl,0,3)=$A4)*(INDEX(Tbl, 0,4)=B$3),0),2),"-")

Fill B4 right into C4.

A5 [array formula]:
=INDEX(Tbl,MATCH(1,(INDEX(Tbl,0,1)=$B$1)
*(COUNTIF(A$4:A4,INDEX(Tbl,0,3))=0),0),3)

Fill B4:C4 down into B5:C5, then fill A5:C5 down as far as needed.

Note: these formulas recalculate V E R Y S L O W L Y !

You may be better off using a pivot table for this.

nikko wrote...
when you do a vlookup - =VLOOKUP($B$1,Tbl,3,0) ; it only returns the very
1st id.
But under owner ABC, i have few Ids to return...

there are 3 lines in the source data for owner ABC
but i only want it to return 2 lines, as one of the id - 1234 is duplicate
(appearing twice)....
....

OK, so you didn't try my formulas and follow the instructions I gave.
I had tested them, and they produced the following result table when
Spreadsheet1!B1 is ABC.

Owner ABC

ID Lic Mnt
1234 10 25
4567 - 50


And when Spreadsheet1!B1 is EFD,

Owner EFD

ID Lic Mnt
4569 - 60
7893 - 50


If you want a different approach, someone else will need to provide it.