Transpose, delete duplicates to appear on Analysis tab from multiple tabs

[Mirian]

Workbook contains 5 sheets (sheet names):
Analysis
Tour
France
Jesuit
Louie

Analysis page:
A B C D E F
1 Merchant Name Total Volume Monthly Amount Sales Code
2 Tour $2824396.97 $2592.64 $49668.07
3 France $16866173.23 $2131.26 $52136.87
4 Jesuit $10 $50
Louie $26011168.43 $21966.21 $29037.61


Tour page:
A B C D
1 Merchant Name Merchant Number Code
2 Tour 444501 5212
3 Tour 444503 5212
4 Tour 444509 5444


France page:

A B C D
1 Merchant Name Merchant Number Code
2 France 444508 5212
3 France 444510 1258
4 France 444523 6666
5 France 444588 5212


Final Analysis page after new formula generated:

A B C D E F G H
1 Merchant Name Total Volume Monthly Amount Sales Code
2 Tour 5212 5444
3 France 5212 1258 6666
4 Jesuit
5 Louie

Let me know if you can help. Thank you very much.

 

[Xiq]

Hi!
I don't really understand what you want, is it every unique code per merchant name?
Also, it seems to me a Pivot Table would be able to handle this easily (if the data is on one sheet anyway).

 

[Abhijeet R. Joshi]

Xiq, what I think Mirian wants is that she wish to lookup for the merchant (Tour, France,etc...) in all three sheets and then give the merchant code [tour = (sheet2)!5212 5444 5212] etc... but she only needs to unique values like 5212 is repeated on the page but its captured only once on the final analysis sheet..

Mirian, please confirm if this is what you are expecting so that we can find a correct solution to it..

 

[SirJB7]

Hi, Mirian!
Agreeing with the 2 previous comments, would you please elaborate and better indeed consider uploading a sample file (including manual examples of desired output if applicable)?. It'd be very useful for those who read this and might be able to help you. Thank you.
Regards!