Finding Inventory Number & Categorizing Item

searchingforhelp

Board Regular
Joined
Nov 11, 2020
Messages
67
Office Version
  1. 365
Platform
  1. Windows
Everyday data will be removed from Sheet2 Column A and replaced with new random data. As the year progresses so will the volume of data.

What I need help with:

  1. A formula that will look at the inventory number in Sheet2 Column A then;
  2. Locate that inventory number in Sheet1 Column A and B, then;
  3. In sheet2 Column B, identify the item (in this instance Apple/ Oranges
Sheet2
Mr Excel.xlsx
AB
1Inventory #Category Apple/ Oranges
2ERT-005656Apple
3ERT-003030Oranges
4ERT-007000
5ERT-003333
6ERT-002374
7ERT-007777
8ERT-004321
9ERT-004545
10ERT-007330
11ERT-002323
Sheet2

Sheet1
Mr Excel.xlsx
AB
1ApplesOranges
2ERT-004321ERT-003030
3ERT-007000ERT-002374
4ERT-005656ERT-004545
5ERT-003333ERT-007777
6ERT-007330ERT-002323
Sheet1


Thanking you in advance for any help you can provide.
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
With only two product (Apples and Oranges), I came up with a possibility.

Book7
AB
1Inventory #Category Apple/ Oranges
2ERT-005656Apples
3ERT-003030Oranges
4ERT-007000Apples
5ERT-003333Apples
6ERT-002374Oranges
7ERT-007777Oranges
8ERT-004321Apples
9ERT-004545Oranges
10ERT-007330Apples
11ERT-002323Oranges
Sheet2Result
Cell Formulas
RangeFormula
B2:B11B2=IF(ISERROR(MATCH(A2,Inventory!$A$1:$A$6,0)),IF(ISERROR(MATCH(A2,Inventory!$B$2:$B$6,0)),"no",Inventory!$B$1),Inventory!$A$1)


Using Inventory as:

ApplesOranges
ERT-004321ERT-003030
ERT-007000ERT-002374
ERT-005656ERT-004545
ERT-003333ERT-007777
ERT-007330ERT-002323
 
Upvote 0
I think this will work with multiple columns (I used 5 but you can change that).

Code:
=INDEX(Products,SUM(MMULT(--(Inventory=A2),TRANSPOSE(SEQUENCE(1,5)))))

Products is Inventory!A1:E1
 
Upvote 0
With only two product (Apples and Oranges), I came up with a possibility.

Book7
AB
1Inventory #Category Apple/ Oranges
2ERT-005656Apples
3ERT-003030Oranges
4ERT-007000Apples
5ERT-003333Apples
6ERT-002374Oranges
7ERT-007777Oranges
8ERT-004321Apples
9ERT-004545Oranges
10ERT-007330Apples
11ERT-002323Oranges
Sheet2Result
Cell Formulas
RangeFormula
B2:B11B2=IF(ISERROR(MATCH(A2,Inventory!$A$1:$A$6,0)),IF(ISERROR(MATCH(A2,Inventory!$B$2:$B$6,0)),"no",Inventory!$B$1),Inventory!$A$1)


Using Inventory as:

ApplesOranges
ERT-004321ERT-003030
ERT-007000ERT-002374
ERT-005656ERT-004545
ERT-003333ERT-007777
ERT-007330ERT-002323

Thank you kweaver. Unfortunately, the formula did not work. The formula is not reading sheet1 data, as there is no reference of sheet1 in the formula. I am working with two sheets. Lastly, is there a possibility to remove the word "inventory" from the formula, and have it just read the columns and rows? I am planning to use this formula for different purposes and having to specify a column name will not be helpful. Thanking you in advance for your time.

What I need help with:

  1. A formula that will look at the inventory number in Sheet2 Column A then;
  2. Locate that inventory number in Sheet1 Column A and B, then;
  3. In sheet2 Column B, identify the item (in this instance Apple/ Oranges
 
Upvote 0
Inventory is simply a NAMED RANGE to make it clearer and easier to type rather than the range itself. It's Sheet1!$A$1:$E$6
You can change that named range to be whatever range you want on Sheet1.
I also created a NAMED RANGE for the products (Apples, etc.) and it's Sheet1!$A$1:$E$1
I changed the sheet tab names as well to hopefully make it easier to follow.
The formula could be:
Code:
=INDEX(Sheet1!$A$1:$E$1,SUM(MMULT(--(Sheet1!$A$1:$E$6=A2),TRANSPOSE(SEQUENCE(1,5)))))

Sheet1:

ApplesOrangesPearsBananas
ERT-004321ERT-003030ERT-99999ERT-11223344
ERT-007000ERT-002374ERT-88888
ERT-005656ERT-004545
ERT-003333ERT-007777
ERT-007330ERT-002323

Sheet2

Book8
AB
1Inventory #Category Apple/ Oranges
2ERT-005656Apples
3ERT-003030Oranges
4ERT-007000Apples
5ERT-003333Apples
6ERT-002374Oranges
7ERT-007777Oranges
8ERT-004321Apples
9ERT-004545Oranges
10ERT-007330Apples
11ERT-002323Oranges
12ERT-99999Pears
13ERT-11223344Bananas
Sheet2
Cell Formulas
RangeFormula
B2:B13B2=INDEX(Products,SUM(MMULT(--(Inventory=A2),TRANSPOSE(SEQUENCE(1,5)))))


If it "doesn't work" -- what error are you getting?
 
Upvote 0
Solution
Inventory is simply a NAMED RANGE to make it clearer and easier to type rather than the range itself. It's Sheet1!$A$1:$E$6
You can change that named range to be whatever range you want on Sheet1.
I also created a NAMED RANGE for the products (Apples, etc.) and it's Sheet1!$A$1:$E$1
I changed the sheet tab names as well to hopefully make it easier to follow.
The formula could be:
Code:
=INDEX(Sheet1!$A$1:$E$1,SUM(MMULT(--(Sheet1!$A$1:$E$6=A2),TRANSPOSE(SEQUENCE(1,5)))))

Sheet1:

ApplesOrangesPearsBananas
ERT-004321ERT-003030ERT-99999ERT-11223344
ERT-007000ERT-002374ERT-88888
ERT-005656ERT-004545
ERT-003333ERT-007777
ERT-007330ERT-002323

Sheet2

Book8
AB
1Inventory #Category Apple/ Oranges
2ERT-005656Apples
3ERT-003030Oranges
4ERT-007000Apples
5ERT-003333Apples
6ERT-002374Oranges
7ERT-007777Oranges
8ERT-004321Apples
9ERT-004545Oranges
10ERT-007330Apples
11ERT-002323Oranges
12ERT-99999Pears
13ERT-11223344Bananas
Sheet2
Cell Formulas
RangeFormula
B2:B13B2=INDEX(Products,SUM(MMULT(--(Inventory=A2),TRANSPOSE(SEQUENCE(1,5)))))


If it "doesn't work" -- what error are you getting?

Sorry for the delay in responding. With a few modifications from what you had provided in post #5, the formula works! Please see below the formula:

=INDEX(Sheet1!$A$1:$B$1,SUM(MMULT(--(Sheet1!$A$1:$B$6000=B2),TRANSPOSE(SEQUENCE(1,2)))))

Last question, is there a way to have the formula results populate in the column without dragging down the rows?

Thanks again for the help.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,144
Members
448,552
Latest member
WORKINGWITHNOLEADER

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top