Matching two ranges based on one common variable

[mrali1987]

Hi there,

I have an onlineshop and I’m buying from two wholesalers, both wholesalers provide me a list with their products incl. prices, descriptions etc. Both use the same unique EAN number for every product. Now I’m trying to match these two lists based on the EAN number to compare prices etc. Since the lists are provides in .xlsx and .csv I was hoping to use Calc. What would be the best way to go about it, or would Base be the more suitable solution?

Example:
Wholesaler 1:
Product ID, Title, Price, EAN
WS1001, Apples, €10, 590012
WS1002, Bananas, €20, 590058
WS1003, Oranges, €30, 590025

Wholesaler 2:
Product ID, Title, Price, EAN
WS2101, Apples, €5, 590012
WS2102, Bananas, €15, 590058
WS2103, Oranges, €25, 590025

The EAN number is unique for every product, regardless of the wholesaler, therefore I would like to compare prices based on this number. I hope I’ve made myself clear and I’d appreciate any help.

Cheers!

[Villeroy]

https://help.libreoffice.org/24.2/en-US/text/scalc/01/04060109.html?DbPAR=CALC#bm_id3158407

[mrali1987]

Thanks for your reply, I worked with the Match and Index functions. It does somewhat what I want but it’s complicated and not quite satisfying. An issue is that not all products exist in both list since they aren’t all sold by both wholesalers. I was hoping for a better solution.

[Villeroy]

@mrali1987

I was hoping for a better solution.

A true database instead of an arithmetic calculator would be a perfect solution for the task.