Return single value by searching multiple criteria in multiple column

[Ramesh h&m]

Hi,

The table formation in thread not working properly, even i used the option, pls advise...

I have a table 5 rows x 7 columns in sheet 1
1 to 6 column named various stationery material descriptions and 7th column mentioned a product code for identifying these 6 descriptions in each (1 to 5) rows.

I am filling a material inward details in sheet 2 and the table Infinite rows x 10 columns
1st column is date, 2nd to 8th column filling the various stationery material descriptions, 9th column is quantity and 10th column i need a formula to return the product code based on the sheet 1.

I am filling a material sold out quantity in Sheet 3.
I need a formula for consolidate the day to day material inward quantity for each product codes.

Kindly do the needful.

[JohnTopley]

Attach a sample workbook (not image).

Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate.

Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[Ramesh h&m]

SCREEN SHOT.JPG

Pls find my screen shot, as the file attachment option not working.

Pls advise

[JohnTopley]

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

"Choose File"

Browse ==>Select file

"Upload"

"Close this window"

"Submit reply"

[Ramesh h&m]

Hi,

Pls find the attached file and advise your valuable solution.

[JohnTopley]

The "normal" process is to input product code and get the detail data rather than the other way round. To obtain the product code means matching all fields B:G in "Product List" with those in "Inward".

and same for "Stock".

Product code

in L2

=IF(B2="","",INDEX('PRODUCT LIST'!$H$2:$H$11,MATCH($D2&$E2&$F2&$G2&$H2&$I2,'PRODUCT LIST'!$B$2:$B$11&'PRODUCT LIST'!$C$2:$C$11&'PRODUCT LIST'!$D$2:$D$11&'PRODUCT LIST'!$E$2:$E$11&'PRODUCT LIST'!$F$2:$F$11&'PRODUCT LIST'!$G$2:$G$11,0)))

...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Stock

in J4

=IF(B4="","",INDEX('PRODUCT LIST'!$H$2:$H$11,MATCH($B4&$C4&$D4&$E4&$F4&$G4,'PRODUCT LIST'!$B$2:$B$11&'PRODUCT LIST'!$C$2:$C$11&'PRODUCT LIST'!$D$2:$D$11&'PRODUCT LIST'!$E$2:$E$11&'PRODUCT LIST'!$F$2:$F$11&'PRODUCT LIST'!$G$2:$G$11,0)))

..confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

in H4

=SUMIF(INWARD!$L$2:$L$1000,$J4,INWARD!$J$2:$J$1000)

[Ramesh h&m]

Hi John,

Its amazing...

I am satisfied as, the quantity updated in "stock" automatically by your great formula.

Also i request that, is it possible to update the B to G & columns (product descriptions) in "stock" sheet

[JohnTopley]

Also i request that, is it possible to update the B to G & columns (product descriptions) in "stock" sheet

I don't understand the above as this detail is in the "Stock" sheet: with what ????

[JohnTopley]

If you want "Stock" to match "Product List" then

in "Stock "

in B4

=IF('PRODUCT LIST'!$B2="","",'PRODUCT LIST'!B2)

copy across to G and down

[Ramesh h&m]

Hi john...

Wow....

Its awesome really...

It will be a big relief for me to managing the stock and i never forget you..

Thanks and thanks a lot...

[JohnTopley]

You are very welcome.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[Ramesh h&m]

Hi John,

I used this chart and i need to add one feature to make it more brilliant.

How to avoid that, adding same product description twice and provide different product code in the Prod

[Ramesh h&m]

Hi John,

I used this chart and i need to add one feature to make it more brilliant.

How to avoid that, adding same product description twice and provide different product code in the "Product list"?

[JohnTopley]

I used this chart and i need to add one feature to make it more brilliant.

????

Please post file.

[Ramesh h&m]

Hi,

How to avoid that, adding same product description twice and provide different product code in the "Product list"?

[JohnTopley]

This in I2 of "Product List"

=IF($H2="","",IF(COUNTIF($H$2:$H2,$H2)>1,"Duplicate Product code",""))

copy down

[Ramesh h&m]

Hi John,

I am asking to avoid, adding a product description, which is already available.

Not repeating the product code, because the product code giving in a sequence order. So it will not get repeated again.

Pls see my attachment Highlighted in yellow.

[JohnTopley]

Use COUNTIFS and check all fields


=IF($H2="","",IF(COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2,.............)>1,"Duplicate Product ",""))

[samba_ravi]

SELECT PRODUCT LIST FROM B2:G50 and use following formula in data validation under custom

=COUNTIFS($B$2:$B2,$B2,$C$2:$C2,$C2,$D$2:$D2,$D2,$E$2:$E2,$E2,$F$2:$F2,$F2,$G$2:$G2,$G2)=1
it will warn you if have the same product discriptions

[Ramesh h&m]

Hi nflsales,

After doing this data validation, I don't see any warning message, once i entered the same product description again.

Pls find my attachment and advise...

[samba_ravi]

remove the Row no 10 or SI 9 Try to re enter from b10:g10

[Ramesh h&m]

Hi nflsales,

Its working and thank you so much...

I am working on the sheet and let me come back any query or make this thread solved.