Matching two ranges based on one common variable

English
#1

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!

#2

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

#3

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.

#4

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

#5

I would suggest the wording “or Calc in combination with Base”. As one of the possible solutions, build a query with the mouse
For your task, I think you would need four queries:

  • a list of products that are only available from one supplier (a list of all EANs and prices marked Wholesaler 1 or Wholesaler 2)
  • a list of products with the same prices, to make a decision on choosing a supplier manually
  • a full list of matching products
  • and the same list, in which only the lines with the best price are left

Base will let you create these four queries in minutes. If you only use Calc, you’ll need to create and debug different formulas for each list. This is not a trivial task, especially for the “records not in another list” query.

#6

I made a first version with Calc and it works so far, I’m still not quite happy.

I don’t have experience with Base (or Access variants) but I do have some experience with PHP and MySQL so I think I should be able to figure that out so I’ll make another version with Base (and Calc) and see what makes me happier.

#7

This is a link to step-by-step instructions on YouTube https://youtu.be/xt0BL8UzCBs?si=ehxyCoDpkUIBj3ya

Perhaps one Calc is really enough for this task. It is enough to mark the records of each of the lists with the Wholesaler number, combine both lists into one long one, sort by EAN and price increase, apply conditional formatting. Then you can immediately see products with a good price (green), with a not so good price (red) and unique (white)

image
image612×505 44.4 KB

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.