How can i extract data that with multiple row headers

M

MZING81

Board Regular
Joined
Mar 20, 2012
Messages
74
HI

I have a large list of 30+ columns, they are divided into 3 cateogries, and each column has 2 row headers. Is there a way to do a lookup or aggregate or index/match function that can use each header as a reference and extract the data, vertically that matches the 2 criterias. I have done it with 2 criterias one in a column and another in a row, but Ihavent been able to pull it off with the 2 criterias within a row.


Thanks
MZING81
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
4/14/20134/14/20134/14/20134/15/20134/15/20134/16/20134/16/20134/17/20134/17/20134/18/20134/18/2013
4/15/2013EMPOLYEESHIFT_TIMEEMPOLYEESHIFT_TIMEEMPOLYEESHIFT_TIMEEMPOLYEESHIFT_TIMEEMPOLYEESHIFT_TIME
04/14/134/16/2013JON DOE 104:00-12:30JON DOE 112:00-20:30JANE DOE 104:00-12:30JON DOE 104:00-12:35JANE DOE 104:00-12:30
4/17/2013JON DOE 204:00-12:31JON DOE 212:00-20:31JANE DOE 204:00-12:31JON DOE 204:00-12:36JANE DOE 204:00-12:31
EXTRACTED DATA BELOW4/18/2013JON DOE 304:00-12:32JON DOE 312:00-20:32JANE DOE 304:00-12:32JON DOE 312:00-20:30JANE DOE 304:00-12:32
EMPLOYEE SHIFT_TIMEJON DOE 404:00-12:33JON DOE 412:00-20:33JANE DOE 404:00-12:33JON DOE 412:00-20:31JANE DOE 404:00-12:33
JON DOE 504:00-12:34JON DOE 512:00-20:34JANE DOE 504:00-12:34JON DOE 512:00-20:32JANE DOE 504:00-12:34
JON DOE 604:00-12:35JON DOE 612:00-20:35JANE DOE 604:00-12:35JON DOE 612:00-20:33JANE DOE 604:00-12:35
JON DOE 704:00-12:36JON DOE 712:00-20:36JANE DOE 704:00-12:36JON DOE 712:00-20:34JANE DOE 704:00-12:36
JON DOE 812:00-20:30JON DOE 812:00-20:37JANE DOE 804:00-12:35JON DOE 812:00-20:35JANE DOE 812:00-20:30
JON DOE 912:00-20:31JON DOE 912:00-20:38JANE DOE 904:00-12:36JON DOE 912:00-20:36JANE DOE 912:00-20:31
JON DOE 1012:00-20:32JON DOE 1004:00-12:32JANE DOE 1012:00-20:30JON DOE 1004:00-12:35JANE DOE 1012:00-20:32
JON DOE 1112:00-20:33JON DOE 1104:00-12:33JANE DOE 1112:00-20:31JON DOE 1104:00-12:36JANE DOE 1112:00-20:33
JON DOE 1212:00-20:34JON DOE 1204:00-12:34JANE DOE 1212:00-20:32JON DOE 1204:00-12:37JANE DOE 1212:00-20:34
JON DOE 1312:00-20:35JON DOE 1304:00-12:35JANE DOE 1312:00-20:33JON DOE 1304:00-12:38JANE DOE 1312:00-20:35
JON DOE 1412:00-20:36JON DOE 1404:00-12:36JANE DOE 1412:00-20:34JON DOE 1404:00-12:39JANE DOE 1412:00-20:36
JON DOE 1512:00-20:37JON DOE 1504:00-12:37JANE DOE 1512:00-20:35JON DOE 1504:00-12:40JANE DOE 1504:00-12:35
JON DOE 1612:00-20:38JON DOE 1604:00-12:38JANE DOE 1612:00-20:36JON DOE 1604:00-12:41JANE DOE 1604:00-12:36

<COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" span=2 width=78><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" span=10 width=101><TBODY>
</TBODY>




dulitul said:
upload a sample pls
Click to expand...
 
Upvote 0
4/14/20134/14/20134/14/20134/15/20134/15/20134/16/20134/16/20134/17/20134/17/20134/18/20134/18/2013
4/15/2013EMPOLYEESHIFT_TIMEEMPOLYEESHIFT_TIMEEMPOLYEESHIFT_TIMEEMPOLYEESHIFT_TIMEEMPOLYEESHIFT_TIME
04/14/134/16/2013JON DOE 104:00-12:30JON DOE 112:00-20:30JANE DOE 104:00-12:30JON DOE 104:00-12:35JANE DOE 104:00-12:30
4/17/2013JON DOE 204:00-12:31JON DOE 212:00-20:31JANE DOE 204:00-12:31JON DOE 204:00-12:36JANE DOE 204:00-12:31
EXTRACTED DATA BELOW4/18/2013JON DOE 304:00-12:32JON DOE 312:00-20:32JANE DOE 304:00-12:32JON DOE 312:00-20:30JANE DOE 304:00-12:32
EMPLOYEE SHIFT_TIMEJON DOE 404:00-12:33JON DOE 412:00-20:33JANE DOE 404:00-12:33JON DOE 412:00-20:31JANE DOE 404:00-12:33
JON DOE 504:00-12:34JON DOE 512:00-20:34JANE DOE 504:00-12:34JON DOE 512:00-20:32JANE DOE 504:00-12:34
JON DOE 604:00-12:35JON DOE 612:00-20:35JANE DOE 604:00-12:35JON DOE 612:00-20:33JANE DOE 604:00-12:35
JON DOE 704:00-12:36JON DOE 712:00-20:36JANE DOE 704:00-12:36JON DOE 712:00-20:34JANE DOE 704:00-12:36
JON DOE 812:00-20:30JON DOE 812:00-20:37JANE DOE 804:00-12:35JON DOE 812:00-20:35JANE DOE 812:00-20:30
JON DOE 912:00-20:31JON DOE 912:00-20:38JANE DOE 904:00-12:36JON DOE 912:00-20:36JANE DOE 912:00-20:31
JON DOE 1012:00-20:32JON DOE 1004:00-12:32JANE DOE 1012:00-20:30JON DOE 1004:00-12:35JANE DOE 1012:00-20:32
JON DOE 1112:00-20:33JON DOE 1104:00-12:33JANE DOE 1112:00-20:31JON DOE 1104:00-12:36JANE DOE 1112:00-20:33
JON DOE 1212:00-20:34JON DOE 1204:00-12:34JANE DOE 1212:00-20:32JON DOE 1204:00-12:37JANE DOE 1212:00-20:34
JON DOE 1312:00-20:35JON DOE 1304:00-12:35JANE DOE 1312:00-20:33JON DOE 1304:00-12:38JANE DOE 1312:00-20:35
JON DOE 1412:00-20:36JON DOE 1404:00-12:36JANE DOE 1412:00-20:34JON DOE 1404:00-12:39JANE DOE 1412:00-20:36
JON DOE 1512:00-20:37JON DOE 1504:00-12:37JANE DOE 1512:00-20:35JON DOE 1504:00-12:40JANE DOE 1504:00-12:35
JON DOE 1612:00-20:38JON DOE 1604:00-12:38JANE DOE 1612:00-20:36JON DOE 1604:00-12:41JANE DOE 1604:00-12:36

<COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 51pt; mso-width-source: userset; mso-width-alt: 2486" width=68><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" span=10 width=101><TBODY>
</TBODY>

<TBODY>
</TBODY>







dulitul said:
upload a sample pls
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Sum Across Multiple Columns with date Critieria Across the header
2
Replies
12
Views
298
ChetanPuri
C
N
  • Solved
Formula to change font color in part of cell only
Replies
2
Views
341
Nathan Asius
N
H
  • Solved
Amend the VBA code to Filter Columns Instead of Rows
Replies
9
Views
163
Akuini
Akuini
H
  • Question
Extract Data from Single Cell. Column is Constant. Number of Rows Involved Changes. Number of Rows Within a Single Cell Can be 1 Up to 9
Replies
6
Views
106
helpmeplz_
H
L
  • Question
Determine match based on if a date range falls within a larger date range :S
Replies
1
Views
63
dermie_72
D

Forum statistics

Threads
1,215,024
Messages
6,122,729
Members
449,093
Latest member
Mnur

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top