Extracting bulk of values from one specific cell to another based on value in third cell

[oskkri]

Hi, I’m Oskar and new to the forums here.

I’m trying to do an inventory of a small warehouse with the help of libreoffice. In our inventory most objects has an Item ID, but some things are too small to have such labels. Therefore in my inventory I have registered them with their name instead, exacly as it’s called in our database.

I have exported the values for the names including item ID from our database, and put them in the same document as the objects registered during our inventory. I now want to extract the Item ID’s like in this example:

The Object “2xXLR till 2xRCA 1-3m” with an empty “Item ID”-field, should have any Item ID from a Database inventory ID field that has the exact same name: “2xXLR till 2xRCA 1-3m”.

Been trying to find an answer and different methods for this for a while by searching this forums, so hope I didn’t miss anything.

Thanks on beforehand!

Libreoffice_example_inventory.ods (38.7 KB)

[Lupp]

What’s the difference between “2x tele” and “2xtele” (e.g.)?
How was the mentioned database created, and to what extent is it binding and/or reliable? It looks a bit as if somebody freely invented the “Names”, and sometimes created a second (third … ?) name for the same “thing”.
What about “Physical inventory” Names not occurring as “Database inventory” Names (59 rows)?

[Steph1]

Here is a tentative solution.
The difficulty comes from the presence of items having multiple occurrences. So first, we must check whether there are less items with the same name in column B than in column D. If this is not the case, we will display “Too many times the same item”. The formula is:
=IF(COUNTIF($B$2:B2,B2)<=COUNTIF($D$2:$D$2314,B2),"xxxx","Too many times the same item")
Second, when we have enough items in D, we search for the first match, then we add the number of times we have already seen this item in column B. So “xxxx” is:
INDEX($E$2:$E$2314,MATCH(B2,$D$2:$D$2314,0)+COUNTIF($B$2:B2,B2)-1)
which gives the final formula:
=IF(COUNTIF($B$2:B2,B2)<=COUNTIF($D$2:$D$2314,B2),INDEX($E$2:$E$2314,MATCH(B2,$D$2:$D$2314,0)+COUNTIF($B$2:B2,B2)-1),"Too many times the same item")
Copy it down the column C and that’s it!
Libreoffice_example_inventory.ods (85.7 KB)

[oskkri]

Thank you SO much! And sorry for being late on the reply actually implemented this directly but had so much on my table that I didnt get to thanking you until now.

You really saved me from a tight spot!