Extract data from a vertical list and transform as dataset [second example]

Hello excel friends
this my second question I need to transform the data set from origial list im colum A into columes C, D as you can see the image that will be my expectes result
Thank you

	A	B	C	D
1	Original Data		Expected result
2	Employ 1		dog	Employ 1
3	dog		cat	Employ 1
4	cat		armadillo	Employ 1
5	armadillo		badger	Employ 2
6	Employ 2		bat	Employ 2
7	badger		bear	Employ 2
8	bat		beaver	Employ 2
9	bear		bullock	Employ 3
10	beaver		camel	Employ 3
11	Employ 3		chimpanzee	Employ 3
12	bullock		dachshund	Employ 3
13	camel		fox	Employ 4
14	chimpanzee		gazelle	Employ 4
15	dachshund		gerbil	Employ 4
16	Employ 4		giraffe	Employ 4
17	fox		goat	Employ 4
18	gazelle		grizzly bear	Employ 4
19	gerbil		guinea pig	Employ 4
20	giraffe		hamster	Employ 4
21	goat		hare	Employ 4
22	grizzly bear		mammoth	Employ 5
23	guinea pig		marmot	Employ 5
24	hamster		mink	Employ 5
25	hare		mole	Employ 5
26	Employ 5		mongoose	Employ 5
27	mammoth		mouse	Employ 5
28	marmot		mule	Employ 5
29	mink
30	mole
31	mongoose
32	mouse
33	mule

Here you go...

Please Login or Register  to view this content.

Hello ¯\_(ツ)_/¯
this code works fine, but as I told you in topic before, I prefer formula because i'm learning formulas, thank you anyway for your Help
Please formulas
Thank You

Did you check back to your previous thread to see I posted a formula solution?

I won't tackle this one until I get some feedback in that thread.

Pete

Hello Pete_UK
as you can see from this image i answered on your Topic, so please can you help me whit this
Thank you

\1

I got the first part of it (i.e. the animals' names), but I couldn't work out an appropriate way of getting the second column before I went to bed last night. I'm about to go out now for most of the day, but I'll pick this up later if no-one else has chipped in.

Pete

Great Thank you Pete

This time I needed two helper columns, so I used F and G, with these formulae in the cells stated:

F2: =IF(A2="","",IF(LEFT(A2,6)="Employ",ROW()-COUNTIF(A$1:A1,"Employ*"),""))

G2: =IF(A2="","",IF(F2="",MAX(G$1:G1)+1,""))

Copy these down to at least the bottom row of your data (i.e. to row 33, or beyond). Then you can use this formula in C2 to get the animal:

=IFERROR(INDEX($A:$A,MATCH(ROWS($1:1),$G:$G,0)),"")

and this one in D2 to get the employer:

=IF(ROWS($1:1)>COUNT(G:G),"",INDEX(A:A,MATCH(ROWS($1:2),F:F)))

Note the first ROWS term has a range of $1:1, whereas the second ROWS term has a range of $1:2. Copy these two down until you start to get blanks. Note that a formula cannot return formatting, so you do not get the colours in column D.

I'm not too happy with the formula in column F, as it relies on "Employ …" being in the cells, whereas in reality I expect you would have actual names, but it works with the data that you supplied.

Hope this helps.

Pete

Woww
Works fine, I knew was impossible with single formula, but looks nice to me, now time to study it
Thank you pete good Job!!
Solved the problem