Age Group and Gender

[pangs]

Hi, I am having trouble sorting out this data, I need to sort it out according to the table and the value in the table should be counted as number "1".
In the first row, the column 4&5 was supposed to be merged but I can't do it here same with 6&7, 8&9.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]DATA[/TD]
[TD]DATA[/TD]
[TD]DATA[/TD]
[TD]Less than 1 year[/TD]
[TD][/TD]
[TD]1 to less than 2 years[/TD]
[TD][/TD]
[TD]2 to less than 5 years[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]GENDER[/TD]
[TD]AGE[/TD]
[TD]AGE GROUP[/TD]
[TD]MALE[/TD]
[TD]FEMALE[/TD]
[TD]MALE[/TD]
[TD]FEMALE[/TD]
[TD]MALE[/TD]
[TD]FEMALE[/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]1[/TD]
[TD]1 to less than 2 years[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]5[/TD]
[TD]2 to less than 5 years[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]0[/TD]
[TD]less than 1 year[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]3[/TD]
[TD]2 to less than 5 years[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]2[/TD]
[TD]2 to less than 5 years[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]M[/TD]
[TD]0[/TD]
[TD]less than 1 year[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]F[/TD]
[TD]1[/TD]
[TD]1 to less than 2 years[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RCBricker]

I copies your data to a new sheet starting in cell A1.

the following formula works with that data

=IF(OR(AND($A3="F",D$2="FEMALE"),AND($A3="M",D$2="MALE")),IF(ISERROR(IF(AND(ISNUMBER(LEFT(D$1,1)*1),AND($B3>=LEFT(D$1,1)*1,$B3<=MID(D$1,16,1)*1)),$B3,"")),IF(AND($B3<1,LEFT(D$1,1)="L"),$B3,""),IF(AND(ISNUMBER(LEFT(D$1,1)*1),AND($B3>=LEFT(D$1,1)*1,$B3<=MID(D$1,16,1)*1)),$B3,"")),"")

 

[Fluff]

Another option

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	DATA	DATA	DATA	Less than 1 year		1 to less than 2 years		2 to less than 5 years
2	GENDER	AGE	AGE GROUP	MALE	FEMALE	MALE	FEMALE	MALE	FEMALE
3	M	1	1 to less than 2 years			1
4	F	5	2 to less than 5 years						1
5	F	0	less than 1 year		1
6	M	3	2 to less than 5 years					1
7	F	2	2 to less than 5 years						1
8	M	0	less than 1 year	1
9	F	1	1 to less than 2 years				1
Sheet1

Cell Formulas
Range	Formula
D3	=IF(AND($C3=D$1,$A3=LEFT(D$2,1)),1,"")
E3	=IF(AND($C3=D$1,$A3=LEFT(E$2,1)),1,"")
F3	=IF(AND($C3=F$1,$A3=LEFT(F$2,1)),1,"")
G3	=IF(AND($C3=F$1,$A3=LEFT(G$2,1)),1,"")
H3	=IF(AND($C3=H$1,$A3=LEFT(H$2,1)),1,"")
I3	=IF(AND($C3=H$1,$A3=LEFT(I$2,1)),1,"")

 

[RCBricker]

Another option

Excel 2013/2016
	A	B	C	D	E	F	G	H	I
1	DATA	DATA	DATA	Less than 1 year		1 to less than 2 years		2 to less than 5 years
2	GENDER	AGE	AGE GROUP	MALE	FEMALE	MALE	FEMALE	MALE	FEMALE
3	M	1	1 to less than 2 years			1
4	F	5	2 to less than 5 years						1
5	F	0	less than 1 year		1
6	M	3	2 to less than 5 years					1
7	F	2	2 to less than 5 years						1
8	M	0	less than 1 year	1
9	F	1	1 to less than 2 years				1
Sheet1

Cell Formulas
Range	Formula
D3	=IF(AND($C3=D$1,$A3=LEFT(D$2,1)),1,"")
E3	=IF(AND($C3=D$1,$A3=LEFT(E$2,1)),1,"")
F3	=IF(AND($C3=F$1,$A3=LEFT(F$2,1)),1,"")
G3	=IF(AND($C3=F$1,$A3=LEFT(G$2,1)),1,"")
H3	=IF(AND($C3=H$1,$A3=LEFT(H$2,1)),1,"")
I3	=IF(AND($C3=H$1,$A3=LEFT(I$2,1)),1,"")

Man talking abut missing the obvious. I didn't even think to use the data in column C to match the headers D to H.

 

[pangs]

Thank you Fluff! This is exactly what I was looking for!

 

[pangs]

Thank you Fluff! This is exactly what I was looking for!

 

[Fluff]

You're welcome & thanks for the feedback