Multi-criteria

[Lifevest]

Microsoft Office Home 2010

Dear Excel Gurus – First and foremost, I bow to your expertise and thank you for any help you may be able to offer, as I am way out of my league with this question.

Description:
I am trying to make sense of a massive data dump I have received. The first image below is a sample of the data, which is 1000+ rows deep and several hundred columns wide. The second image is what I am trying to do with the data (e.g. an output sheet for each metric).

Dummy Data

https://drive.google.com/file/d/0B_Xdrauu1Dk8VjBNQzNxZEU4YlU/edit?usp=sharing

Desired Output Format

https://drive.google.com/file/d/0B_Xdrauu1Dk8NHE5WXhEU3BMS2c/edit?usp=sharing

The data represents health information for each individual by date. However, the dates for each individual vary, as well as the various metrics tracked (e.g. cigarettes consumed, DBP, SBP etc.)

Below is a link to some dummy data (sheet titled: Data Dump) and two sheets that show how I am hoping to segment the data (Output DBP, Output SBP) but have no clue how.

The output sheets would have the email of the individual, the health metric and date that it can search the "Data Dump" sheet and return the metrics in the proper dates.

Though the dummy spreadsheet only has two output sheet (DBP and SBP), I would make sheets for all of the various metrics (cigarettes consumed, DBP, Height, Hip Circumference, SBP, Waist Circumference, Weight).

Link to Spreadsheet
https://drive.google.com/file/d/0B_Xdrauu1Dk8QTlXYTBMLVF2ZVU/edit?usp=sharing

Any help you might be able to provide would be exceptionally appreciated, as I am in way over my head (my skills haven’t yet progressed beyond vlookup / hlookup).

With appreciation,
Lifevest

 

[cyrilbrd]

Hi, If your data was arranged in a different manner that allows extraction then you could use a simple formula or pivot table to retrieve what you require.
Given in A1:

Excel 2010
	A	B	C	D	E	F	G	H	I	J
1	First name	Last name	address	date	Consumption	DBP	Height	Hip_Cir	SBP	mass
2	Bob	Smith	bob.smith@test.com	3/18/2014	0	68	6'5"		120	200
3	Bob	Smith	bob.smith@test.com	3/21/2014	0	69			121	201
4	Jill	Howzer	Jill.Howzer@test.com	3/19/2014	0	80	4'11"	35	120	120
5	Jill	Howzer	Jill.Howzer@test.com	3/12/2014	0	80		36	121	119
6	Jill	Howzer	Jill.Howzer@test.com	3/15/2014	0	80		37	122	118
7	Jill	Howzer	Jill.Howzer@test.com	3/30/2014	0	80		38	123	117
Lifevest

Given in L2:

Excel 2010
	L	M	N	O	P	Q	R	S
2	Email		3/12/2014	3/15/2014	3/18/2014	3/19/2014	3/21/2014	3/30/2014
3	bob.smith@test.com	DBP			68		69
4	Jill.Howzer@test.com	DBP	80	80		80		80
Lifevest

Formula in N3 is =IFERROR(INDEX($E$2:$J$7,MATCH(1,INDEX(($D$2:$D$7=N$2)*($C$2:$C$7=$L3),0),0),MATCH($M3,$E$1:$J$1,0)),"") copied down and right till needed.

Please take note of the way the data is arranged.

 

[Lifevest]

Thank you so much cyrilbrd! I will see if I can get the data outputted in that manner.

 

[Lifevest]

Once I was able to get the data output set as you suggested, it worked like a charm. Thanks again cyrilbrd!

 

[cyrilbrd]

Once I was able to get the data output set as you suggested, it worked like a charm. Thanks again cyrilbrd!

Most welcome, glad it worked for you.
Thanks for the feedback.