Formula needed (index/match probably)

[balages74]

Hi All,

I need some help with constructing a formula.
Basically I have a three column table, one column is transfer document, one is shop and one is goods code in the shop.
What I'd like to see is what is the goods code of the same goods in the different shops. The only connection between the goods (to know that it's the same goods is the transfer document number. I'd like to create a formula that shows all combinations of the same goods amongst the shops (it can also happen that the same goods have multiple code in the same shop due to multiple transfers). Tried to do it with index/match but it gets very complicated and still I have duplicates at the end both vertically and horizontally.

Example of what I'd like to achieve:

This is where I got so far, but still some duplicates

Thank you in advance,

 

[Aladin Akyurek]

Would you please post the data of the pictures in an Excel readable form? These require re-typing...

 

[balages74]

Sure, is it OK to do thru drive?
https://drive.google.com/open?id=1szqvWhyR0-lCLVicgLoPy0-f4UK4i0Ua

Thank you

 

[Aladin Akyurek]

Thanks, but there is no desired output specified. You don't have to put up formulas. Such does not help understanding, input >> output most often does.

 

[balages74]

OK, thanks. Here is an example without formulas:
https://drive.google.com/open?id=1Lzt559ozTWGuKhAOKUhlwlhRUxpC3_6P
On columns A,B,C the data input is. I'd like to achieve whats on the right columns (from column F)
Basically the mat doc (column A) links 2 materials (column C) together meaning they are the same materials
What I'd like to know is which materials are the same in different shops (as their code differs). There can be multiple documents linking material A-B then B-C, then I'd like to see that A-B-C are all the same.
Thank you

 

[Aladin Akyurek]

Would the following output satisfy your need?

Row\Col	F​	G​	H​	I​
1​	shop/code	code1	code2	code3
2​	ShopA	DFcode1	DFcode2	DFcode3
3​	ShopB	code1	code2	code3
4​	ShopC	Ccode1	Ccode2

 

[balages74]

Thanks for your reply! Unfortunately not. What I need to know is not which shops has which goods, but I need to know which goods are equivalent amonst the shops different codes.
Please see this example:

mat doc	Shop	mat code
3456	ShopC	Ccode2
3456	ShopA	DFcode2
4567	ShopC	Ccode2
4567	ShopA	DFcode3
5678	ShopB	code3
5678	ShopC	Ccode2

<tbody>
</tbody>

Where mat docs are equal those are the same goods. So ShopC-Ccode2 is the same as ShopA-DFcode2 (both has mat doc 3456). Also ShopB-code3 is the item as ShopC-Ccode2 as they share mat doc 5678. So ShopC-Ccode2 is equal to ShopA-DFcode2 and also equal to ShopB-code3
What I'd like to have is one line per item that contains all of it's different mat codes for the same item:
ShopC-Ccode2 ShopA-DFcode2 ShopB-code3

Given this table:

mat doc	Shop	mat code
1234A	ShopA	DFcode1
1234A	ShopB	code1
2345	ShopB	code2
2345	ShopC	Ccode1
3456	ShopC	Ccode2
3456	ShopA	DFcode2
4567	ShopC	Ccode2
4567	ShopA	DFcode3
5678	ShopC	Ccode2
5678	ShopB	code3
6789	ShopC	Ccode1
6789	ShopB	code2

<tbody>
</tbody>

the result needs to be this:

Shop1	code1	Shop2	code2	Shop3	code3	Shop4	code4
ShopA	DFcode1	ShopB	code1
ShopC	Ccode2	ShopA	DFcode2	ShopA	DFcode3	ShopB	code3
ShopB	code2	ShopC	Ccode1

<tbody>
</tbody>

 

[Aladin Akyurek]

Are you sure? The output I have shown has the same pairings as yours and is created with a formula system which constructs it using mat doc associations...

 

[balages74]

Yes unfortunately
For example mat doc 3456 pairs Ccode2-DFcode2, then 4567 pairs Ccode2-DFcode3, then 5678 pairs Ccode2-code3. So there should be Ccode2-DFcode2-DFcode3-code3 somewhere since I'm interested in which materials are the same. Your table shows which store has which mats, but what I'd like to know is what the mat code of the very same material is in the different shops. Shops are not even too important, I just need to know all the codes the same item is called in different shops (stocks).