Mar 20 2019 09:40 AM
Hi
i have a stock counter sheet to use it with barcode reader
i have the items in other sheet and wont to get the data when i input the barcode to get the prodect ID, the prodect name and the UOM
i have onle one Id for the items and 4 to 5 barcode depends on the UOM
I need the index understand the barcode and get the data for me
i try but no success
how can i do that
thank for the help
best regards
saeedom
there is 2 Pic. of the sheets
Mar 20 2019 10:01 AM
Hi@Saeedom , your barcodes are texts or numbers? It looks like texts based on length. Use concatenate then (&). And in your case it will be array formula (Ctrl+Shift+Enter)
Mar 20 2019 12:07 PM
Mar 21 2019 02:50 AM
Hi
I try like this (&) and it didn't work
also (:) and not working
only work if I choose one column
is there a way to fix this or there is another formula
please help
Mar 21 2019 03:37 AM
It is not the INDEX/MATCH that is in error.
It is the way in which you are treating the bar-codes as numbers. You say the bar-codes are 3 to 5 digits long but your picture shows 16 digit codes which is more than can be stored as an Excel number. Your formula also shows bar-codes being added '+' which does not make much sense. @Sergei Baklan's suggestion of concatenation '&' would provide a better basis for comparison.
p.s. If you really wish to combine bar-codes as arithmetic values then you would need
= codeA * 10^LEN(codeB) + codeB
but care needs to be taken that the resulting number must not go beyond 15 digits.
Mar 21 2019 04:54 AM
SolutionSorry, I have only just understood what it is you are trying to do. I believe you are trying to find a match in any one of 5 columns and to return the product name. You would need 5 separate MATCH formulas for that and then take the maximum of the 5 row-indices before using INDEX.
Alternatively you could use non-standard formulas such as
= LOOKUP( 1, 1 / MMULT(
SIGN( Table_ProductList[[Product Barcode 1 BOX]:[Product Barcode 5 pallet]] = [@[Product Barcode]] ),
{1;1;1;1;1} ),
Table_ProductList[Item] )
or
= CONCAT(
IF( Table_ProductList[[Product Barcode 1 BOX]:[Product Barcode 5 pallet]] = [Product Barcode],
Table_ProductList[Item], "" ) )
Mar 21 2019 06:48 AM
Thanks for the help
yes that what I need to do
the lookup formula work for me the other one not working but thats ok. i need only one
Now i try to use the formula for the product barcode UOM (Unit Of Measurement) to the same table, but it gives wrong answer. How i can fix the formula to get only the header name of the specified barcode column.
Mar 25 2019 07:06 AM
Hi
can you please help with the UOM so when i reed a box barcode i can see the unit there or roll and get that data from the header barcode that i have
i try the formula you put and try to change it but i get wrong answer
thanks from the heart
my regards
saeed
Mar 26 2019 04:20 AM
HI
can you please help with UOM i need it to just take the header name back to the count list sheet in the UOM column
Mar 26 2019 05:55 AM - edited Mar 26 2019 06:09 AM
Hi @Saeedom ,
Do I understand correctly what other words you'd like to return in which column of the list the barcode was found?
I didn't test, but Peter's formula shall work
= LOOKUP( 1, 1 / MMULT( SIGN( Table_ProductList[[Product Barcode 1 BOX]:[Product Barcode 5 pallet]] = [@[Product Barcode]] ), {1;1;1;1;1} ), list!$C$2:$G$2 )
Mar 26 2019 06:57 AM
@Saeedom , that will be like
=INDEX(list!$C$2:$G$2, SUMPRODUCT((Table_ProductList = [@[Product Barcode]] )*COLUMN(Table_ProductList))-COLUMN(Table_ProductList[[#Headers],[Product Barcode 1 BOX]])+1)
Mar 26 2019 10:03 AM
thank you from the heart
it works with some change
I'm really cant't thank you enough
and also can't forget Mr.@Peter Bartholomew
all the best for you
my regards
Mar 26 2019 01:28 PM
This is a problem where a sample Excel file would have helped immensely. Earlier I assumed that each barcode you seek could appear within any one of 5 columns, setting it outside the normal VLOOKUP scenario. I now believe you also wish to return the header for the column in which you find the barcode. My approach is first to focus upon these 5 columns of Table_ProductList. Because the headings are so long I defined the name 'TableData' to represent the array of reference barcodes. This refers to:
=Table_ProductList[[Product Barcode 1 Box]:[Product Barcode 5 Pallet]]
The barcode I am seeking to match is a single (relative) value from the other table that I called 'Barcode':
=Table2[@[Product Barcode]]
The next step is to compare each element of the array for a match to the barcode, using 1 to indicate the matching code and 0 for all others. That I called 'arrayMatches' which refers to the formula
= SIGN( TableData=Barcode ).
This table is the basis by which both the record (as above) and the heading may be returned. Unfortunately spreadsheets are so focussed upon interactive use that there is a dearth of operators that act upon a 2D array by row or by column. MMULT may be unique in that regard. To calculate the sum over columns for each row, one has 'columnMatches'
= 1 / MMULT( arrayMatches, {1;1;1;1;1} )
This column has 1 for the row containing the matched barcode and a #DIV/0! error for all other rows. LOOKUP can be used to return the corresponding value from any other column
= LOOKUP( 1, columnMatches, Table_ProductList[Item] )
To return the heading from the array of matches is a very similar process except it is each column that has to be summed, returning a row with a single 1 for the match and #DIV/0! error for other columns, namely 'rowMatches'
= 1 / MMULT( U, arrayMatches )
The other thing one needs to evaluate this formula is the row array of 1s, 'U', the is the same length and the table has rows. 'U' is given by
= TRANSPOSE( SIGN( ROW(TableData) ) )
The appropriate table header is returned by what is, in effect, an HLOOKUP, namely
= LOOKUP( 1, rowMatches, HeadingUIC )
where 'HeadingUIC' are the headings of the 5 columns
=Table_ProductList[[#Headers],[Product Barcode 1 Box]:[Product Barcode 5 Pallet]]
I appreciate this is heavy-going and involves approaches that are alien to normal spreadsheet development but I hope it will provide some insight.
Mar 21 2019 04:54 AM
SolutionSorry, I have only just understood what it is you are trying to do. I believe you are trying to find a match in any one of 5 columns and to return the product name. You would need 5 separate MATCH formulas for that and then take the maximum of the 5 row-indices before using INDEX.
Alternatively you could use non-standard formulas such as
= LOOKUP( 1, 1 / MMULT(
SIGN( Table_ProductList[[Product Barcode 1 BOX]:[Product Barcode 5 pallet]] = [@[Product Barcode]] ),
{1;1;1;1;1} ),
Table_ProductList[Item] )
or
= CONCAT(
IF( Table_ProductList[[Product Barcode 1 BOX]:[Product Barcode 5 pallet]] = [Product Barcode],
Table_ProductList[Item], "" ) )