Index Match data to provide multiple results in columns

[lomond44]

I need some help with a spreadsheet that contains 2 worksheets.

The first sheet contains part numbers and the assemblies that they are used in. Each part number can be used in many assemblies.

On the 2nd sheet is a list of just the part numbers without duplicates. The aim is to create columns to the right of each part number that identifies all the assemblies that the part is used in.

I have looked at this link How To Return Multiple Match Values in Excel Using INDEX-MATCH or VLOOKUP | eImagine Technology Group but I do not want the results to be down a column but to go across the row.

Any help appreciated

 

[roaldstrand]

Ok, nice job guys, some really powerful and streamlined code in this thread for sorting data on single and multiple criteria types.

But lets take it a step further, and this is where I got seriously stuck using embedded array index match functions to try to get rid of non-uniques.

So, here's the question, imagine, now going back to the first page, and the first table, that there is not just one mouse named Mickey but (say 4) but we only want to identify the first occurrence, then subsequently bypass it each subsequent time it pops up; as well for any other duplicate.

How would we modify this code to remove non-uniques?

 

[Aladin Akyurek]

@roaldstrand

Which post number are you referring to?

 

[roaldstrand]

Using the example I linked to in my post, the data and results should look like this:

Part No	Assembly	Lookup	Match 1	Match 2
Duck	Daffy		Duck	Daffy
Mouse	Mickey		Mouse	Mickey	Minnie
Mouse	Minnie		Bunny	Bugs
Bunny	Bugs		Pig	Porky
Pig	Porky		Dog	Goofy	Pluto
Dog	Goofy
Dog	Pluto

<tbody>
</tbody>

This one, sorry for being vague.

 

[roaldstrand]

So here's an example of what I was asking, and the output that I'm looking for: basically we take the original formula, but remove any subsequent non-uniques from it.

Part No	Assembly	Lookup	Match 1	Match 2
Duck	Daffy	Duck	Daffy
Mouse	Mickey	Mouse	Mickey	Mickey
Mouse	Mickey	Bunny	Bugs
Bunny	Bugs	Pig	Porky
Pig	Porky	Dog	Goofy	Pluto
Dog	Goofy
Dog	Pluto
Mouse	Mickey
Mouse	Mickey
Mouse	Minnie
Dog	Pluto
Dog	Pluto

<tbody>
</tbody>

 

[roaldstrand]

Sorry, i was editing and timer ran out on making edits. Here's what I'm looking for.

Animal	Name	Lookup	Match 1	Match 2
Duck	Daffy	Duck	Daffy
Mouse	Mickey	Mouse	Mickey	Minnie
Mouse	Mickey	Bunny	Bugs
Mouse	Mickey	Pig	Porky
Bunny	Bugs	Dog	Goofy	Pluto
Pig	Porky
Pig	Porky
Dog	Goofy
Dog	Goofy
Dog	Goofy
Dog	Pluto
Dog	Pluto

<tbody>
</tbody>

[/QUOTE]

 

[Aladin Akyurek]

So here's an example of what I was asking, and the output that I'm looking for: basically we take the original formula, but remove any subsequent non-uniques from it.
[...]

Sorry, i was editing and timer ran out on making edits. Here's what I'm looking for.

[...]

Row\Col	A​	B​	C​	D​	E​	F​
1​	Part No	Assembly		Part No	Part of
2​	Duck	Daffy		Duck	Daffy
3​	Mouse	Mickey		Mouse	Mickey	Minnie
4​	Mouse	Mickey		Bunny	Bugs
5​	Mouse	Mickey		Pig	Porky
6​	Bunny	Bugs		Dog	Goofy	Pluto
7​	Mouse	Minnie
8​	Pig	Porky
9​	Pig	Porky
10​	Dog	Goofy
11​	Dog	Goofy
12​	Dog	Goofy
13​	Dog	Pluto
14​	Dog	Pluto

E2, control+shift+enter, not just enter, copied across, and down:

=IFERROR(INDEX($B$2:$B$14,SMALL(IF(FREQUENCY(IF($A$2:$A$14=$D2,
   IF($B$2:$B$14<>"",MATCH($B$2:$B$14,$B$2:$B$14,0))),
   ROW($B$2:$B$14)-ROW($B$2)+1),ROW($B$2:$B$14)-ROW($B$2)+1),
   COLUMNS($E2:E2))),"")

 

[roaldstrand]

I am highly impressed.

Great, elegant solution,

Thanks!

 

[Aladin Akyurek]

I am highly impressed.

Great, elegant solution,

Thanks!

You are welcome.

 

[avn80]

Hi Guys..I am hoping what I have as a query would be a continuation on this thread itself.


I have a sheet (Sheet1) with a list of companies (112 companies) and its market cap.


On another tab in the same worksheet I have some other companies (762 companies) from which I hope to match some companies to the companies on Sheet1 as suitable peers. The condition is that the market cap should be in a range of +/-30% of the market cap of the companies in Sheet1


To give an example

Sheet 1 looks like this

Company Names	Mkt.Cap	30%	-30%
A	10000	13000	7000
B	20000	26000	14000
C	130000	169000	91000
D	20000	26000	14000
E	50000	65000	35000
F	30000	39000	21000
G	40000	52000	28000

<tbody>
</tbody>

Sheet 2 looks like:

Company Name	Market Cap
AAA	170000
BBB	20000
CCC	40000
DDD	2000
EEE	30000
FFF	1000
GGG	60000
HHH	70000
III	19000
JJJ	29000
KKK	30000

<tbody>
</tbody>

For each company in Sheet 1, there will be more than one peer. The peers for A should return Companies from AAA to KKK which have a market cap in the range between 13000 and 7000.

In my best case scenario, a peer for company A would not be considered as a peer for company B so as not to affect further calculations.

Any help would be welcome.

Thanks in advance.

 

[Aladin Akyurek]

[...]

[...]

In my best case scenario, a peer for company A would not be considered as a peer for company B so as not to affect further calculations.
[...]

Are you saying that DDD should not peered with A as well as with B?