Hi Gents,
Please help me in this issue, I need to make a report for a specific date, As per the attached, I need to type the date @ B22 and all data for that date start to appear at A27 and continue at the following cells, please help me but I don't want a pivot table.
You can create advance data filter to view the desired output and reuse that filter again with help of a macro attached to a shape.
Please find the attached sheet.
Hope it helps.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Here is a vba solution
Please Login or Register to view this content.
Alan עַם יִשְׂרָאֵל חַי
Change an Ugly Report with Power Query
Database Normalization
Complete Guide to Power Query
Man's Mind Stretched to New Dimensions Never Returns to Its Original Form
Moreover if you are interested to get this done with the help of a formula, here is the formula......
Then drag this formula to right and down to get the desired output.Please Login or Register to view this content.
Please find the attached sheet.
Last edited by sktneer; 10-25-2013 at 11:17 PM.
See if this works (using sample provided)
in A27:
Formula:
Please Login or Register to view this content.
Drag down as far as needed (I would probably go a 100 or 1000 rows to be safe)
in B27:
=IFERROR(INDEX($B$1:$B$17,SUMPRODUCT(LARGE(($A$1:$A$17=$B$22)*ROW($B$1:$B$17),ROWS($1:1)))),"")
fill down as far as previous formula
(NOTE- This puts it in the reverse order of the appearance in the table, but is faster than trying to match appearance order, if appearance order is needed, I will adjust it to that)
in C27:
Formula:Please Login or Register to view this content.
Again extend down as far as A column formula goes
see Attached
Hope this helps
A picture may be worth a thousand words, BUT, a sample Workbook is worth a thousand screenshots!
-Add a File - click advanced (next to quick post), scroll to manage attachments, click, select add files, click select files, select file, click upload, when file shows up at bottom left, click done (bottom right), click submit
-To mark thread Solved- go top of thread,click Thread Tools,click Mark as Solved
If you received helpful response, please remember to hit the * of that post
Thank you all for your help,
sktneer
your two solutions are amazing, could you please explain them both
Glad to help you. Please correct one error in the formula I mentioned above......
Change reference within index part from INDEX(A2:A17, To INDEX(A$2:A$17,
Rest part of the formula will remain same.
In your sample book you have very small data set so probably you will not notice an error. But if you have a large data set, you will notice some error. So change the formula as suggested so that it will work on any size of data set.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks