formula to autofill with condition and skip blank cells

[TheLSD]

I have the master sheet and data sheet (more than 1 but same format)

This is the example of the data from the sheet

Project	Details
Project A
	Laptop
	Printer
	Camera
Blank
	Phone
	Projector
Project C
	Implementation
	Debug
	Smart TV

what I need to do is to copy the project and skip the second column

if the project is blank then copy all the second column

but if the next first column contains words again, then copy and skip the second column

also, skip the blank rows and the formula able to accommodate more than one data sheets

expected outcome:

Project
Project A
Project B
Phone
Projector
Project C

I have the master sheet and data sheet (more than 1 but same format)

This is the example of the data from the sheet

Project	Details
Project A
	Laptop
	Printer
	Camera
Blank
	Phone
	Projector
Project C
	Implementation
	Debug
	Smart TV

what I need to do is to copy the project and skip the second column

if the project is blank then copy all the second column

but if the next first column contains words again, then copy and skip the second column

also, skip the blank rows and the formula able to accommodate more than one data sheets

expected outcome:

Project
Project A
Project B
Phone
Projector
Project C

the formula I use right now: =IF('Data1'!C7<>0;'Data1'!C7;'Data1'!E7)

the result from my formula:

Project
Project A
Laptop
Printer
Camera
0
0
Phone
Projector
0
Project C
Implementation
Debug
Smart TV

Note: the data rows are not always the same number with every data sheet (i.e. data1 100 rows, data2 400 rows, so on and I need the formula to work continuously through all the sheet (i.e. after the data from data1 finished, the formula move to data2, so on)

thank you

 

[BSALV]

here with formulas and 2 named ranges but it can also be done in VBA

Map1
	C	D	E	F	G	H	I	J	K
1						5	Project
2						6	Project A
3						7,1	Laptop
4						8,1	Printer
5		Project	Details			9,1	Camera
6		Project A				11	Blank
7			Laptop			12,1	Phone
8			Printer			13,1	Projector
9			Camera			15	Project C
10						16,1	Implementation
11		Blank				17,1	Debug
12			Phone			18,1	Smart TV
13			Projector			#GETAL!	-
14						#GETAL!	-
15		Project C				#GETAL!	-
16			Implementation			#GETAL!	-
17			Debug			#GETAL!	-
18			Smart TV			#GETAL!	-
19						#GETAL!	-
20						#GETAL!	-
21						#GETAL!	-
22						#GETAL!	-
23						#GETAL!	-
Blad1

Cell Formulas
Range	Formula
H1:H23	H1	=SMALL(IF(Project<>"",ROW(Project), IF(OFFSET(Project,,1,,)<>"",ROW(Project)+0.1,"")),ROW())
I1:I23	I1	=IF(ISNUMBER(H1),OFFSET(Project_Start,-ROW(Project_Start)+H1,H1<>TRUNC(H1),1,1),"-")

Named Ranges
Name	Refers To	Cells
Project_Start	=Blad1!$D$5	H1:I23

 

[TheLSD]

here with formulas and 2 named ranges but it can also be done in VBA

Map1
	C	D	E	F	G	H	I	J	K
1						5	Project
2						6	Project A
3						7,1	Laptop
4						8,1	Printer
5		Project	Details			9,1	Camera
6		Project A				11	Blank
7			Laptop			12,1	Phone
8			Printer			13,1	Projector
9			Camera			15	Project C
10						16,1	Implementation
11		Blank				17,1	Debug
12			Phone			18,1	Smart TV
13			Projector			#GETAL!	-
14						#GETAL!	-
15		Project C				#GETAL!	-
16			Implementation			#GETAL!	-
17			Debug			#GETAL!	-
18			Smart TV			#GETAL!	-
19						#GETAL!	-
20						#GETAL!	-
21						#GETAL!	-
22						#GETAL!	-
23						#GETAL!	-
Blad1

Cell Formulas
Range	Formula
H1:H23	H1	=SMALL(IF(Project<>"",ROW(Project), IF(OFFSET(Project,,1,,)<>"",ROW(Project)+0.1,"")),ROW())
I1:I23	I1	=IF(ISNUMBER(H1),OFFSET(Project_Start,-ROW(Project_Start)+H1,H1<>TRUNC(H1),1,1),"-")

Named Ranges
Name	Refers To	Cells
Project_Start	=Blad1!$D$5	H1:I23

Thank you for the answer, but if there's text inside column C, only copy the column C. But when column C blank, then move to column E and copy until its blank and go back to column C to copy if there's text there, but if not then continue copy from column E until its blank. Your answer works perfectly but it needs a little adjustment for my requirements.
But if you have any suggestions to tweak the table in order to meet my requirements, feel free to drop them!
If there's any update for it, do please give me. Appreciate it!

 

[Peter_SSs]

Where did you get that highlighted value from?

 

[TheLSD]

View attachment 56971

Where did you get that highlighted value from?

Ah, so sorry my bad. I mistype it. Should be Phone after Project A.

 

[Peter_SSs]

See if you could use something like this.
I have used column F in the data sheets as a helper column. It could be any column and it could be hidden once populated if you want.

22 02 04.xlsm
	C	D	E	F
5				4
6	Project		Details	0
7	Project A			1
8			Laptop	1
9			Printer	1
10			Camera	1
11				1
12				1
13			Phone	2
14			Projector	3
15				3
16	Project C			4
17			Implementation	4
18			Debug	4
19			Smart TV	4
20				4
Data1

Cell Formulas
Range	Formula
F5	F5	=MAX(F6:F20)
F7:F20	F7	=F6+AND(OR(C7<>"",INDEX(C$7:C7,AGGREGATE(14,6,(ROW(E$7:E7)-ROW(E$7)+1)/(E$7:E7=""),1))=""),LEN(C7&E7)>0)

22 02 04.xlsm
	C	D	E	F
5				10
6	Project		Details	4
7	Project D			5
8			Item 1	5
9				5
10			Item 2	6
11			Item 3	7
12			Item 4	8
13				8
14	Project E			9
15			Item 5	9
16			Item 6	9
17				9
18			Item 7	10
19				10
20				10
Data2

Cell Formulas
Range	Formula
F5	F5	=MAX(F6:F20)
F6	F6	=Data1!F5
F7:F20	F7	=F6+AND(OR(C7<>"",INDEX(C$7:C7,AGGREGATE(14,6,(ROW(E$7:E7)-ROW(E$7)+1)/(E$7:E7=""),1))=""),LEN(C7&E7)>0)

22 02 04.xlsm
	A
1	Project
2	Project A
3	Phone
4	Projector
5	Project C
6	Project D
7	Item 2
8	Item 3
9	Item 4
10	Project E
11	Item 7
12
13
Master

Cell Formulas
Range	Formula
A2:A13	A2	=IFNA(INDEX(Data1!C$7:C$20&Data1!E$7:E$20,MATCH(ROWS(A$2:A2),Data1!F$7:F$20,0)),IFNA(INDEX(Data2!C$7:C$20&Data2!E$7:E$20,MATCH(ROWS(A$2:A2),Data2!F$7:F$20,0)),""))

 

[TheLSD]

See if you could use something like this.
I have used column F in the data sheets as a helper column. It could be any column and it could be hidden once populated if you want.

22 02 04.xlsm
	C	D	E	F
5				4
6	Project		Details	0
7	Project A			1
8			Laptop	1
9			Printer	1
10			Camera	1
11				1
12				1
13			Phone	2
14			Projector	3
15				3
16	Project C			4
17			Implementation	4
18			Debug	4
19			Smart TV	4
20				4
Data1

Cell Formulas
Range	Formula
F5	F5	=MAX(F6:F20)
F7:F20	F7	=F6+AND(OR(C7<>"",INDEX(C$7:C7,AGGREGATE(14,6,(ROW(E$7:E7)-ROW(E$7)+1)/(E$7:E7=""),1))=""),LEN(C7&E7)>0)

22 02 04.xlsm
	C	D	E	F
5				10
6	Project		Details	4
7	Project D			5
8			Item 1	5
9				5
10			Item 2	6
11			Item 3	7
12			Item 4	8
13				8
14	Project E			9
15			Item 5	9
16			Item 6	9
17				9
18			Item 7	10
19				10
20				10
Data2

Cell Formulas
Range	Formula
F5	F5	=MAX(F6:F20)
F6	F6	=Data1!F5
F7:F20	F7	=F6+AND(OR(C7<>"",INDEX(C$7:C7,AGGREGATE(14,6,(ROW(E$7:E7)-ROW(E$7)+1)/(E$7:E7=""),1))=""),LEN(C7&E7)>0)

22 02 04.xlsm
	A
1	Project
2	Project A
3	Phone
4	Projector
5	Project C
6	Project D
7	Item 2
8	Item 3
9	Item 4
10	Project E
11	Item 7
12
13
Master

Cell Formulas
Range	Formula
A2:A13	A2	=IFNA(INDEX(Data1!C$7:C$20&Data1!E$7:E$20,MATCH(ROWS(A$2:A2),Data1!F$7:F$20,0)),IFNA(INDEX(Data2!C$7:C$20&Data2!E$7:E$20,MATCH(ROWS(A$2:A2),Data2!F$7:F$20,0)),""))

OH MY GOD! YOU'RE A LIFESAVER SIR! THANK YOU VERY MUCH!
I mingle with this problem for about a week and thank God you answer what I need!
Truly, thank you

 

[Peter_SSs]

You're welcome. Glad it works for you. Thanks for the follow-up.