Segregating Data based on varying number of columns

[murexcel]

Hi, we're undertaking a data collection drive for a community project. We have a data collection software that records data in below format.

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1	GROUP ID	FLAT	BUILDING NAME	STATUS	MALE1	MALE1 ID	MALE2	MALE2 ID	MALE3	MALE3 ID	FEMALE1	ID1	FEMALE2	ID2	FEMALE3	ID3	CHILD1	ID1	CHILD2	ID2	CHILD3	ID3
2	1234	105	ABCD	YES	MR. X	123456	MR. Y	123457	MR. Z	123458	MS. X	11111	MS. Y	8888	MS. Z	7777	MSTR X	9999	MSTR Y	7777	MSTR Z	5555
3	1235	106	ABCD	YES	MR.X	122336					MS. X	22222					MSTR X	79654
Sheet1

We need that data to be segregated in the below format.

Book1
	A	B	C	D	E	F	G
1	GROUP ID	FLAT	BUILDING NAME	STATUS	ID	NAME	GENDER
2	1234	105	ABCD	YES	123456	MR. X	MALE1
3					123457	MR. Y	MALE2
4					123458	MR. Z	MALE3
5					11111	MS. X	FEMALE1
6					8888	MS. Y	FEMALE2
7					7777	MS. Z	FEMALE3
8					9999	MSTR X	CHILD1
9					7777	MSTR Y	CHILD2
10					5555	MSTR Z	CHILD3
11
12	1235	105	ABCD	YES	122336	MR. X	MALE1
13					22222	MS. X	FEMALE1
14					79654	MSTR X	CHILD1
Sheet2

The problem is that the data columns from E would vary based on the number of members, so we need a formula that ignores blanks and segregates data in the above format.

We tried a couple of things, but weren't able to achieve the desired results. Any help is greatly appreciated.


EDIT:
Would also like to add that the number of columns after E are not fixed. There could be n number of members for each gender type.

 

[murexcel]

Hello, would appreciate any help on this.

 

[Fluff]

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Segregating Data based on varying number of columns
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.

 

[fjns]

Hi, see in the linked files (with 4 tech worksheets) a possible solution for Office 2010...

The formulas used in the tables...
tech1!A2: =LOWER(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(INDEX(Source!$1:$1,,ROW())," ",""),"0",""),"1",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")) (Range: A2:A50 ...)
tech1!B2: =IF(A2="","",IF(OR(A2="male",A2="female",A2="child"),1,0)) (Range: B2:B50 ...)
tech1!C2: =IF(A2="","",IF(B2=0,0,SUM(B$2:B2))) (Range: C2:C50 ...)
tech1!D2: =SUM(B:B)+4
tech2!E2: =IF(INDEX(tech1!$A:$A,COLUMN())="","",IF(AND(INDEX(tech1!$B:$B,COLUMN())=1,INDEX(tech1!$B:$B,COLUMN()+1)=0),IF(OR(INDEX(Source!2:2,COLUMN())="",INDEX(Source!2:2,COLUMN()+1)=""),0,1),0)) (Range: E2:BB30 ...)
tech3!A2: =SUM(D:D)
tech3!B2: =IF(Source!A2="","",1) (Range: B2:B30 ...)
tech3!C2: =IF(B2="","",SUM(B$2:B2)) (Range: C2:C30 ...)
tech3!D2: =IF(B2="","",MAX(SUM(tech2!2:2),1)+1) (Range: D2:D30 ...)
tech3!E2: =IF(B2="","",SUM(D$1:D1)+2) (Range: E2:E30 ...)
tech4!E2: =IF(INDEX(tech1!$A:$A,COLUMN())="","",IF(tech3!$B2="","",IF(tech2!E2=0,0,SUM(tech2!$E2:E2)))) (Range: E2:BB30 ...)
Segregating!A2: =IF(ROW()>tech3!A$2,"",IFERROR(INDEX(Source!A:A,INDEX(tech3!C:C+1,MATCH(ROW(),tech3!E:E,0))),"")) (Range: A2:A30 ...)
Segregating!B2: =IF(ROW()>tech3!A$2,"",IFERROR(INDEX(Source!B:B,INDEX(tech3!C:C+1,MATCH(ROW(),tech3!E:E,0))),"")) (Range: B2:B30 ...)
Segregating!C2: =IF(ROW()>tech3!A$2,"",IFERROR(INDEX(Source!C:C,INDEX(tech3!C:C+1,MATCH(ROW(),tech3!E:E,0))),"")) (Range: C2:C30 ...)
Segregating!D2: =IF(ROW()>tech3!A$2,"",IFERROR(INDEX(Source!D:D,INDEX(tech3!C:C+1,MATCH(ROW(),tech3!E:E,0))),"")) (Range: D2:D30 ...)
Segregating!E2: =IF(ROW()>tech3!A$2,"",IFERROR(INDEX(INDIRECT("Source!"&INDEX(tech3!C:C,MATCH(ROW(),tech3!E:E))+1&":"&INDEX(tech3!C:C,MATCH(ROW(),tech3!E:E))+1),MATCH(ROW()-INDEX(tech3!E:E,MATCH(ROW(),tech3!E:E))+1,INDIRECT("tech4!"&INDEX(tech3!C:C,MATCH(ROW(),tech3!E:E))+1&":"&INDEX(tech3!C:C,MATCH(ROW(),tech3!E:E))+1),0)+1),"")) (Range: E2:E30 ...)
Segregating!F2: =IF(ROW()>tech3!A$2,"",IFERROR(INDEX(INDIRECT("Source!"&INDEX(tech3!C:C,MATCH(ROW(),tech3!E:E))+1&":"&INDEX(tech3!C:C,MATCH(ROW(),tech3!E:E))+1),MATCH(ROW()-INDEX(tech3!E:E,MATCH(ROW(),tech3!E:E))+1,INDIRECT("tech4!"&INDEX(tech3!C:C,MATCH(ROW(),tech3!E:E))+1&":"&INDEX(tech3!C:C,MATCH(ROW(),tech3!E:E))+1),0)),"")) (Range: F2:F30 ...)
Segregating!G2: =IF(ROW()>tech3!A$2,"",IFERROR(INDEX(Source!$1:$1,MATCH(ROW()-INDEX(tech3!E:E,MATCH(ROW(),tech3!E:E))+1,INDIRECT("tech4!"&INDEX(tech3!C:C,MATCH(ROW(),tech3!E:E))+1&":"&INDEX(tech3!C:C,MATCH(ROW(),tech3!E:E))+1),0)),"")) (Range: G2:G30 ...)

Segregating.xlsx
SegregatingN.xlsx