Hi All,
can anybody help me to set a file as per my desired required DATA. please see the attached file and see the sheet result WANT. dont consider sheet 2 DATA. work only main DATA.
Thanks.
Printable View
Hi All,
can anybody help me to set a file as per my desired required DATA. please see the attached file and see the sheet result WANT. dont consider sheet 2 DATA. work only main DATA.
Thanks.
Please try
A2
=IFERROR(INDEX('Main-DATA'!B:B,AGGREGATE(15,6,ROW('Main-DATA'!$C$2:$C$9999)/NOT(MMULT(--(LEFT('Main-DATA'!$C$2:$C$9999,4)={"bill",""}),{1;1})),ROWS(A$2:A2)))&"","")
B2:J2
=IFERROR(INDEX('Main-DATA'!C:C,AGGREGATE(15,6,ROW('Main-DATA'!C$2:C$9999)/(LEFT('Main-DATA'!$C$2:$C$9999)="m")/ISNUMBER('Main-DATA'!$E$2:$E$9999),ROWS(B$2:B2))),"")
F2
=IFERROR(INDEX('Main-DATA'!G:G,AGGREGATE(15,6,ROW('Main-DATA'!G$2:G$9999)/(LEFT('Main-DATA'!$C$2:$C$9999)="m")/ISNUMBER('Main-DATA'!$E$2:$E$9999),ROWS(F$2:F2))-1),"")
Copy A2:J2 down
I use custom format # to hide 0 at column D:G
hi Bo_Ry,
please see the pic some imp record are incorrect.
Please provide correct answer and highlight row from Main-DATA that data come from.
please see the original data sheet against the IMP of main data sheet.
If you are using Excel 2013 and above you can use "POWER Query"
Hi, Cak,
Excel 2010
Please try again
A2
Formula:=IF(COUNTIF(A$1:A1,LOOKUP("z",'Main-DATA'!B$1:INDEX('Main-DATA'!B:B,AGGREGATE(15,6,ROW('Main-DATA'!B$2:B$9999)/MMULT(--(LEFT('Main-DATA'!$C$2:$C$9999)={"m","s","a"}),{1;1;1})/ISNUMBER('Main-DATA'!$E$2:$E$9999),ROWS(A$2:A2))))),"",LOOKUP("z",'Main-DATA'!B$1:INDEX('Main-DATA'!B:B,AGGREGATE(15,6,ROW('Main-DATA'!B$2:B$9999)/MMULT(--(LEFT('Main-DATA'!$C$2:$C$9999)={"m","s","a"}),{1;1;1})/ISNUMBER('Main-DATA'!$E$2:$E$9999),ROWS(A$2:A2)))))
B2:J2
=IFERROR(INDEX('Main-DATA'!C:C,AGGREGATE(15,6,ROW('Main-DATA'!C$2:C$9999)/MMULT(--(LEFT('Main-DATA'!$C$2:$C$9999)={"m","s","a"}),{1;1;1})/ISNUMBER('Main-DATA'!$E$2:$E$9999),ROWS(B$2:B2))),"")
F2
=IFERROR(INDEX('Main-DATA'!G:G,AGGREGATE(15,6,ROW('Main-DATA'!G$2:G$9999)/MMULT(--(LEFT('Main-DATA'!$C$2:$C$9999)={"m","s","a"}),{1;1;1})/ISNUMBER('Main-DATA'!$E$2:$E$9999),ROWS(F$2:F2))-1),"")
Hi Bo_Ry,
Excellent work, only one IMP No miss, but forget.
your work is very smart. i spent too much time to set it.
Thanks.
can you explain this formula? if you dont mind.
Thanks.
The main part is column C
MMULT(--(LEFT('Main-DATA'!$C$2:$C$9999)={"m","s","a"}),{1;1;1})
(LEFT('Main-DATA'!$C$2:$C$9999)="m")+(LEFT('Main-DATA'!$C$2:$C$9999)="s")+(LEFT('Main-DATA'!$C$2:$C$9999)="a")
We only want rows that column C that start with "m" or "s" or "a"
Above 2 formula will give result 1 if start with "m" or "s" or "a" and 0 if not
Row()/MMULT() gives row number that start with "m" or "s" or "a" and if not give #DIV/0
AGGREGATE(15,6,Row()/MMULT(),k) give a small number that not include error #DIV/0, we use this row with Index to get result from column B:J
Except for column F that need to offset -1 row, so -1 adds after aggregate.
Column A is quite complicated, too hard to explain.
After review Isnumber is not needed, we can remove Isnumber from all formula.
hi Bo_Ry,
Thanks for explanation, big formula is to difficult for understand. i apply it on formula sheet. can you tell me where i am missing to find out IMP No. i got item but miss last one. please tell me where i am missing?
Please take a moment to read the forum rules and then amend your thread title to something descriptive of your problem - not what you think the answer might be. (think google search terms?). Once you have done this please send me a PM and I will remove this request. (Also, include a link to your thread - copy from the address bar)
Many members search our previous posts, and thread titles play a big part of the search. I doubt anybody would do a search based on your title? ;)
To change a Title on your post, click EDIT POST then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.
(note: this change is not optional ;) )
Also, Sorry for off-topic interjection:
Although there is no official rule regarding this behavior, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.
I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
Hi FDibbins,
i know very well about this. this is not a training center but in this topic i want some extra knowledge regarding the formula. actually our routine life we have to face so many different challenges to work with different situation. so that, i request to Bo_Ry to explain this because i have to apply this formula in other situation. if i put a new thread for this, i think this is not a suitable.
Thanks for your rules guidance.
I am not asking you to start a new thread, I am asking, firstly, to change your thread title to something that actually describes your request, and secondly, to explain HERE in the thread, what that problem is, and what you expect.
Dont make members open your file, just to see what you are asking
Ok, i understand.
please see my new TITLE.
Without seeing what you want to do, I cannot say if that title is more descriptive or not, sorry.
i changed new tiltle in title thread, but not showing?
Title:
Data Required in a easy way in a complex file.
Yes, I can see you changed the title (you need to do it in the 1st post though)
Sorry to be such a pain, but I am still waiting to see you describe what your question is, and what you expect?
Please see the correction in blue.
G2
=IFERROR(IF(COUNTIF(G$1:G1,LOOKUP("z",A$2:INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$102)/MMULT(--(LEFT($B$2:$B$102)={"m","s","r","y"}),{1;1;1;1})/ISNUMBER($D$2:$D$102),ROWS(G2:G$2))))),"",LOOKUP("z",A$2:INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$102)/MMULT(--(LEFT($B$2:$B$102)={"m","s","r","y"}),{1;1;1;1})/ISNUMBER($D$2:$D$102),ROWS(G2:G$2))))),"")
H2
=IFERROR(INDEX(B:B,AGGREGATE(15,6,ROW(B$2:B$102)/MMULT(--(LEFT($B$2:$B$102)={"m","s","R","y"}),{1;1;1;1})/ISNUMBER($D$2:$D$102),ROWS(B2:B$2))),"")
Thanks a lot Bo_Ry.