• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Displaying Data in a different column based on a condition

P

pr4peace

New Member
Hi All,


I am in the process of preparing daily , weekly and monthly reports. To prepare number heavy reports I used a lot of help from these forums to guide me on the same. I have a similar problem now , only that now it is a text heavy report.


I need help on how to return on say the 2nd or 3rd column which contains text when the criteria is matching. Also, what if there are more than 1 entry which matches, how do I get all of those in that report in consecutive lines. I hope I am being clear.


Example would be say testing of compressive strength of concrete. I want to make a report of all lab testing conducted on ONE day .The report should report all numerical data and ALSO the location of the concrete taken for the cube and the Quality controllers comments on the same. Both of which are text and more than 10 cubes are tested everyday meaning a multiple line report. HELP !!!!!!!


Please see attached the example file. I am looking at similar setup to sumifs when using numbers .


https://www.dropbox.com/s/i0vo4itaesciec6/quality-example.xls


Thanks in advance.


Prashanth
 
Fastest way would be to create a PivotTable. You could set the Date as the Page Field, and then fill in the row fields with columns of your textual data. If you have any numerical data that you need to analyze (sum, average, max, etc) put them in the Data field area.
 
Hi Luke,


Thanks for the suggestion.


Pivot Table is an option, but I need to to make it a customized A3 sized report, meaning it contains a whole lot of other materials which also have their own report template. I dont like Pivot table since it has its own mind : . Formulas feel better and controllable. Any formula to do the same?


Thanks in adavnce,


Prashanth
 
Hi, pr4peace!

Why not just select row 2 in "concrete cube" sheet, go to Data tab, Sort & Filter group, Filter, to set auto filter on?

Then just filter by desired column B (Testing date) value.

Regards!
 
Hmm. Reading your problem again, it looks a lot like this thread:

http://chandoo.org/forums/topic/index-match-with-multiple-results


Using INDEX would solve the issue of returning multiple columns, and the use of the array formula I described would let you pull the multiple results. Make sure you read the article http://chandoo.org/wp/2011/11/18/formula-forensics-003/ which describes how the formula works.
 
Hi SirJBy,


Thanks for your suggestion, but i still need to make a sort everytime for a new date and a different material. Since I have many materials , I need a single summary sheet which changes based on One value i.e todays(or any other)date.


Luke M,


Thanks a lot for your immediate reply, the formula looks too complicated for me, I am going to take some more time to decode the formulas in the links you sent me. I really appreciate the help.


Thanks,


Prashanth
 
Hi Luke M,


Supercool.


I did exactly as the formula said and it worked like a charm :)


Thank you soo much.


Bye

Prashanth
 
You must log in or register to reply here.
Back
Top