Dear Members, hope my query is just a child's play for you all.
I have data
excel.png
So, i want to pick out only that Bill Of Entry in which specified Part name is not out of stock.
I tried Index, Match, Vlookup but in vain.
Please help.
Thanks in advance.
Regards
Krish.
The easiest Method is Pivot Table.
And if you wish to go for formula approach only then just attach a sample workbook.
Please attach a sample workbook with enough data to make it clear what is needed. 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 demonstrated, mock them up manually if needed. Remember to desensitize the data.
Click on GO ADVANCED and use the paperclip icon to open the upload window.
View Pic
Note: Please don't attach documents containing confidential data like (address, telephone, ID#s, etc.).
If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
Mark your thread as Solved
If the suggestion helps you, then Click *below to Add Reputation
Thanks for the reply. Pivot table is one option i will keep in mind. But i want to automate in the invoice sheet. So like whenever i enter the part name, i want the excel to search the stock availability and pull out the Bill Of Entry. Attached in the workbook.Originally Posted by :) Sixthsense :)
Please help.
Thanks in advance
Regards
Krish
Little bit of confusion because Bumper is having 1BOE and 2BOE as BOE how to show the multiple item in E12 cell of Invoice and which one needs to be shown?
Please show the expected output too…
Sorry that i had confused you by giving only little info. Please refer attachment again.
In E12 Cell of Invoice Sheet - Array Formula - Requires CTRL+SHIFT+ENTER
=IFERROR(INDEX('Data Sheet'!$A$2:$A$7,SMALL(IF(('Data Sheet'!$B$2:$B$7=C12)*('Data Sheet'!$E$2:$E$7>0),ROW('Data Sheet'!$E$2:$E$7)-MIN(ROW('Data Sheet'!$E$2:$E$7))+1),1)),"")
Drag it down…
Array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.
Thanks Genius. Could you please care to explain?
Glad it helps you and thanks for the feedback and rep
=IFERROR(INDEX('Data Sheet'!$A$2:$A$7,SMALL(IF(('Data Sheet'!$B$2:$B$7=C12)*('Data Sheet'!$E$2:$E$7>0),ROW('Data Sheet'!$E$2:$E$7)-MIN(ROW('Data Sheet'!$E$2:$E$7))+1),1)),"")
INDEX(Result_Range, row_num, [column_num_If_Result_Range_Is_Multiple_Column])
'Data Sheet'!$A$2:$A$7 - Result_Range
INDEX('Data Sheet'!$A$2:$A$7
IF(('Data Sheet'!$B$2:$B$7=C12)*('Data Sheet'!$E$2:$E$7>0),ROW('Data Sheet'!$E$2:$E$7)-MIN(ROW('Data Sheet'!$E$2:$E$7))+1
IF(('Data Sheet'!$B$2:$B$7=C12)*('Data Sheet'!$E$2:$E$7>0),
If the above conditions are true then get the row numbers of the matching data
ROW('Data Sheet'!$E$2:$E$7)
The below will get the 2 as result because the data starts from second row
MIN(ROW('Data Sheet'!$E$2:$E$7))+1
SMALL(formula_here,1) will get the small row number of the matching results.
Adding small in row_num of Index function will match the row number in Result_Range and get the result…
If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.
Just one clarification here. Can i add one more condition with AND in the above statement to match color?Glad it helps you and thanks for the feedback and rep
=IFERROR(INDEX('Data Sheet'!$A$2:$A$7,SMALL(IF(('Data Sheet'!$B$2:$B$7=C12)*('Data Sheet'!$E$2:$E$7>0),ROW('Data Sheet'!$E$2:$E$7)-MIN(ROW('Data Sheet'!$E$2:$E$7))+1),1)),"")
INDEX(Result_Range, row_num, [column_num_If_Result_Range_Is_Multiple_Column])
'Data Sheet'!$A$2:$A$7 - Result_Range
INDEX('Data Sheet'!$A$2:$A$7
IF(('Data Sheet'!$B$2:$B$7=C12)*('Data Sheet'!$E$2:$E$7>0),ROW('Data Sheet'!$E$2:$E$7)-MIN(ROW('Data Sheet'!$E$2:$E$7))+1
IF(('Data Sheet'!$B$2:$B$7=C12)*('Data Sheet'!$E$2:$E$7>0),
Yes you can add use * which works as AND operator in Array Formula's
Thank you so much.
Hi, it was awesome, but one small bug. It is not considering the oldest Bill Of Entry (i.e. one with least stock).
can you please throw some light? I have attached the file.
In your formula just change the SMALL to LARGE and check…
You are just amazing!!!. I am very poor in excel. So sad. Would like to take tuition from you
Glad it helps you and thanks for the feedback
Just keep turned on here and read all posts and the replies and you will become a master in excel.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks