HI,
we have a data where we maintain in and out and some other details, file is attached
Now we want a filter or any other formula so that we can get only data which send but not recd. back
help pls
there are two sheets one is data and second is result sheet,
we need the result on second sheet not in same sheet
Result Sheet
b2=FERROR(INDEX(Data!$B$2:$F$1000,MATCH('Result Sheet'!$A2,Data!$A$2:$A$1000,0),MATCH('Result Sheet'!B$1,Data!$B$1:$F$1,0))&"","")
copy across and down
Thanks CARACALLA,
checking this and update you
not working, may some type error
"not working" doesn't tell us anything. Please post the sheet.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
I spotted the typo... Ajay... you should have spotted it yourself!!
=IFERROR(INDEX(Data!$B$2:$F$1000,MATCH('Result Sheet'!$A2,Data!$A$2:$A$1000,0),MATCH('Result Sheet'!B$1,Data!$B$1:$F$1,0))&"","")
hi, posted formula in B2 (Result Sheet) but no not get required result
It works perfectly.... but you do need to have something other than empty cells to match. The purple cells in the results sheet must contain something!!!!
o.k,
but in Result sheet, in Column A value from where we get data, we want filtered data from data sheet, we have not anything in Result Sheet, All the data in result sheet coming from Data Sheet
you can check my result sheet, in result sheet we have put the required result,
its a dummy data, we have large data, approx 2000 rows
Last edited by Ajay45822; 06-24-2021 at 06:24 AM.
Type a number in the red column of the results sheet and you will have the corresponding values
Thanks CARACALLA,
we can understand that if we put a value in column a then we will get corresponding values but it will not solve the problem, we don't need to put number or anything else, we required a formula which can provide the same result as in Result sheet,
but our prob is how can we know which number we put, we have approx 1500 numbers and multiple entry
we required a formula which can check all the details (i mean "Sent and Recd" and provide the only that data which we have sent but not recd) and give result from Data sheet which we have in result sheet,
Result Sheet
A2=IFERROR(INDEX(Data!A$2:A$100,AGGREGATE(15,6,ROW(Data!A$2:A$100)-ROW(Data!A$2)+1/(Data!$C$2:$C$100="sent"),ROWS(Data!$A$2:A2)))&"","")
Copy across and down
Thank you very much CARACALLA,
its solved my problem a little only one point is pending in your result sheet
with the formula we get in column A
2 sent kk546 rock
4 sent kk547 mark
5 sent kk547 Bruce
6 sent kk547 flip
9 Sent kk547 mark
11 Sent kk548 mark
we have only one problem in this and that is, we want to remove point number 2 and 4 because this is outgoing and we have received back this material in "Data!A11:F11 and Data!A9:F9 so if we remove then we will get
product code detail 1 detail 2 detail 3 detial 4
5 sent kk547 Bruce
6 sent kk547 flip
9 Sent kk547 mark
11 Sent kk548 mark
kindly check the attached sheet
Data is data sheet
Result Sheet - which we are getting from the formula
required this result - this result required,
help pls
Last edited by Ajay45822; 06-24-2021 at 07:38 AM.
Can we get the results through formula or required a vba code
This proposal employs a helper column (G) on the Data sheet.
The helper column could be moved and/or hidden for aesthetic purposes and is populated using:Formula:
Please Login or Register to view this content.
The formula on the Result sheet is:Formula:Please Login or Register to view this content.
Let us know if you have any questions.
Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks