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?

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][/tr][tr][td]

1​

[/td][td]shop/code[/td][td]code1[/td][td]code2[/td][td]code3[/td][/tr]
[tr][td]

2​

[/td][td]ShopA[/td][td]DFcode1[/td][td]DFcode2[/td][td]DFcode3[/td][/tr]
[tr][td]

3​

[/td][td]ShopB[/td][td]code1[/td][td]code2[/td][td]code3[/td][/tr]
[tr][td]

4​

[/td][td]ShopC[/td][td]Ccode1[/td][td]Ccode2[/td][td][/td][/tr]
[/table]

 

[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:
[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl63, width: 64"]mat doc[/TD]
[TD="class: xl63, width: 64"]Shop[/TD]
[TD="class: xl63, width: 64"]mat code[/TD]
[/TR]
[TR]
[TD="class: xl65"]3456[/TD]
[TD="class: xl65"]ShopC[/TD]
[TD="class: xl65"]Ccode2[/TD]
[/TR]
[TR]
[TD="class: xl65"]3456[/TD]
[TD="class: xl65"]ShopA[/TD]
[TD="class: xl65"]DFcode2[/TD]
[/TR]
[TR]
[TD="class: xl65"]4567[/TD]
[TD="class: xl65"]ShopC[/TD]
[TD="class: xl65"]Ccode2[/TD]
[/TR]
[TR]
[TD="class: xl65"]4567[/TD]
[TD="class: xl65"]ShopA[/TD]
[TD="class: xl65"]DFcode3[/TD]
[/TR]
[TR]
[TD="class: xl65"]5678[/TD]
[TD="class: xl65"]ShopB[/TD]
[TD="class: xl65"]code3[/TD]
[/TR]
[TR]
[TD="class: xl65"]5678[/TD]
[TD="class: xl65"]ShopC[/TD]
[TD="class: xl65"]Ccode2[/TD]
[/TR]
</tbody>[/TABLE]

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:
[TABLE="width: 192"]
<tbody>[TR]
[TD="class: xl66, width: 64"]mat doc[/TD]
[TD="class: xl66, width: 64"]Shop[/TD]
[TD="class: xl66, width: 64"]mat code[/TD]
[/TR]
[TR]
[TD="class: xl65"]1234A[/TD]
[TD="class: xl65"]ShopA[/TD]
[TD="class: xl65"]DFcode1[/TD]
[/TR]
[TR]
[TD="class: xl65"]1234A[/TD]
[TD="class: xl65"]ShopB[/TD]
[TD="class: xl65"]code1[/TD]
[/TR]
[TR]
[TD="class: xl65"]2345[/TD]
[TD="class: xl65"]ShopB[/TD]
[TD="class: xl65"]code2[/TD]
[/TR]
[TR]
[TD="class: xl65"]2345[/TD]
[TD="class: xl65"]ShopC[/TD]
[TD="class: xl65"]Ccode1[/TD]
[/TR]
[TR]
[TD="class: xl65"]3456[/TD]
[TD="class: xl65"]ShopC[/TD]
[TD="class: xl65"]Ccode2[/TD]
[/TR]
[TR]
[TD="class: xl65"]3456[/TD]
[TD="class: xl65"]ShopA[/TD]
[TD="class: xl65"]DFcode2[/TD]
[/TR]
[TR]
[TD="class: xl65"]4567[/TD]
[TD="class: xl65"]ShopC[/TD]
[TD="class: xl65"]Ccode2[/TD]
[/TR]
[TR]
[TD="class: xl65"]4567[/TD]
[TD="class: xl65"]ShopA[/TD]
[TD="class: xl65"]DFcode3[/TD]
[/TR]
[TR]
[TD="class: xl65"]5678[/TD]
[TD="class: xl65"]ShopC[/TD]
[TD="class: xl65"]Ccode2[/TD]
[/TR]
[TR]
[TD="class: xl65"]5678[/TD]
[TD="class: xl65"]ShopB[/TD]
[TD="class: xl65"]code3[/TD]
[/TR]
[TR]
[TD="class: xl65"]6789[/TD]
[TD="class: xl65"]ShopC[/TD]
[TD="class: xl65"]Ccode1[/TD]
[/TR]
[TR]
[TD="class: xl65"]6789[/TD]
[TD="class: xl65"]ShopB[/TD]
[TD="class: xl65"]code2[/TD]
[/TR]
</tbody>[/TABLE]

the result needs to be this:
[TABLE="width: 512"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Shop1[/TD]
[TD="class: xl65, width: 64"]code1[/TD]
[TD="class: xl65, width: 64"]Shop2[/TD]
[TD="class: xl65, width: 64"]code2[/TD]
[TD="class: xl65, width: 64"]Shop3[/TD]
[TD="class: xl65, width: 64"]code3[/TD]
[TD="class: xl65, width: 64"]Shop4[/TD]
[TD="class: xl65, width: 64"]code4[/TD]
[/TR]
[TR]
[TD="class: xl66"]ShopA[/TD]
[TD="class: xl66"]DFcode1[/TD]
[TD="class: xl66"]ShopB[/TD]
[TD="class: xl66"]code1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
[TR]
[TD="class: xl66"]ShopC[/TD]
[TD="class: xl66"]Ccode2[/TD]
[TD="class: xl66"]ShopA[/TD]
[TD="class: xl66"]DFcode2[/TD]
[TD="class: xl66"]ShopA[/TD]
[TD="class: xl66"]DFcode3[/TD]
[TD="class: xl66"]ShopB[/TD]
[TD="class: xl66"]code3[/TD]
[/TR]
[TR]
[TD="class: xl66"]ShopB[/TD]
[TD="class: xl66"]code2[/TD]
[TD="class: xl66"]ShopC[/TD]
[TD="class: xl66"]Ccode1[/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[TD="class: xl66"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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).