Calc: convert cells to matching values from another sheet

[PetrBřeň]

Hello,

basically I am switching from one ecommerce solution to another, and I need to export the products and import them to the new one. Both ecommerce solutions offer export and import to / from XLSX, however, the categories are written in a very different way.

The “old” format has separate tables for categories and products:

CategoryName    |       CatID   |       ParentID
---------------------------------------------------
Food            |       1       |       0
Fruits          |       2       |       1
Vegetables      |       3       |       1
Health          |       4       |       0


Product         |       Category
-----------------------------------
Apple           |       2
Orange          |       2,4
Carrot          |       3

The “new” format does not use category IDs and has category names in the same table with products:

Product |       Category1               |       Category2
-----------------------------------------------------------
Apple   |       Food > Fruits           |
Orange  |       Food > Fruits           |       Health
Carrot  |       Food > Vegetables       |

Is there a way to convert the old format to the new one, assigning the category names from their IDs etc., either using LO Calc’s built-in functionality or perhaps via something like Pandas in Python?

Thank you very much for any hint and have a nice weekend.

Petr Břeň

[LeroyG]

I think that LOOKUP is part of the answer.

[PetrBřeň]

Thank you. I think VLOOKUP in connection with regular expressions is what I’m looking for. But I’m still not sure how to deal with several comma-separated category IDs in a single cell, which is what is used in the “old” format.

[EarnestAl]

In the old table the comma separated Category numbers could be separated into new cells by Data > Text to Columns but it might not suit as it will overwrite any data in the column(s) to the right.

[LeroyG]

Move (or copy) CategoryName to the right of ParentID.

EDIT: Using REGEX and no need to separate Category data in columns.

Separate Category data in columns:
ecommerce text to columns.png

Use these formulas:

• F11: =IFERROR(VLOOKUP(IFERROR(REGEX(B11;".+?(?=,*+[0-9]*)");"");$B$3:$D$6;3;0)&IFERROR(" > "&VLOOKUP(VLOOKUP(IFERROR(REGEX(B11;".+?(?=,*+[0-9]*)");"");$B$3:$D$6;2;0);$B$3:$D$6;3;0);"");"")
• G11: =IFERROR(VLOOKUP(IFERROR(REGEX(B11;"(?<=,).+$");"");$B$3:$D$6;3;0)&IFERROR(" > "&VLOOKUP(VLOOKUP(IFERROR(REGEX(B11;"(?<=,).+$");"");$B$3:$D$6;2;0);$B$3:$D$6;3;0);"");"")
  
• F11: =IFERROR(VLOOKUP(B11;$B$3:$D$6;3;0)&IFERROR(" > "&VLOOKUP(VLOOKUP(B11;$B$3:$D$6;2;0);$B$3:$D$6;3;0);"");"")
• G11:=IFERROR(VLOOKUP(C11;$B$3:$D$6;3;0)&IFERROR(" > "&VLOOKUP(VLOOKUP(C11;$B$3:$D$6;2;0);$B$3:$D$6;3;0);"");"")

See Sheet2 of sample file.

More LibreOffice Help on Text to Columns, VLOOKUP, Addresses and References, Absolute and Relative, IFERROR, and REGEX. List of ICU Regular Expressions.

Made with LibreOffice 6.4.7.2 (x86); OS: Windows 6.1.

---

Add Answer is reserved for solutions. If you think the answer is not satisfactory, add a comment below, or click edit (below your question) to add more information. Thanks.

Check the mark ( → ) to the left of the answer that solves your question.

If the answer helped you, you can mark the up arrow () that is on the left (to vote, you need to have karma of at least 5).

[EarnestAl]

@LeroyG You’ve done a lot of work there, you deserve some recognition. Al

[LeroyG]

@EarnestAl, …and a lot of learn. Thanks for the upvote.

[PetrBřeň]

Wow! Thank you.

[LeroyG]

You’re welcome!