How to extract information from the same column

[K0st4din]

Hello, everyone,
I've been struggling for several days and I can't find a solution on one of my tables.
I'm asking for your help, because I'm going crazy with her.
In one column I have a lot of information, which I try to arrange part of it in different columns.
I haven't tried anything, but obviously nothing comes to my mind how to create it.
I will attach an example to make it clearer what I am trying to do.
In column B2:B I have addresses, street names of people, districts, Phones and under these phones I have Names again.
This is where I try to extract from the same column. If in a cell it sees this Name next to it in the next cell to put the phone.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in
We cannot copy from an image like you have provided.

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[K0st4din]

Hi, I think I managed the plugin to display the table! And now everything must be as it should be!

test name lek.xlsm
	B	C	D	E	F	G	H	I
1
2	2. joki moki ood					Penko ivanov topalov - lekar lekar	Telefon: 08554555855, 55558888555
3	oblast: london, obshtina Qmbol, naseleno mqsto: gr. London
4	adres: nqkakyw si 23
5	Upravitel: imeto na liceto I nqkoj drug
6	Telefon: 08554555855, 55558888555
7	1. Penko ivanov topalov - lekar lekar
8	2. Drago Drasov Minev - lekar lekar
9	3. Blago Ivanow Nqkoi - lekar lekar
10
11	3. kukuruki duduruki ood					dido mesoto hapvane - lekar lekar	Telefon: 08554555855, 55558888555
12	oblast: london, obshtina Qmbol, naseleno mqsto: gr. London
13	adres: nqkakyw 723
14	Upravitel: imeto na liceto I nqkoj drug
15	Telefon: 08554555855, 55558888555
16	1. dido mesoto hapvane - lekar lekar
Sheet1

 

[Peter_SSs]

Thanks for the XL2BB data and updating your profile.

If your sample data is representative with the phone number in the row above the name then this should do it for you.

K0st4din.xlsm
	B	C	D	E	F	G	H
1
2	2. joki moki ood					Penko ivanov topalov - lekar lekar	Telefon: 08554555855, 55558888555
3	oblast: london, obshtina Qmbol, naseleno mqsto: gr. London
4	adres: nqkakyw si 23
5	Upravitel: imeto na liceto I nqkoj drug
6	Telefon: 08554555855, 55558888555
7	1. Penko ivanov topalov - lekar lekar
8	2. Drago Drasov Minev - lekar lekar
9	3. Blago Ivanow Nqkoi - lekar lekar
10
11	3. kukuruki duduruki ood					dido mesoto hapvane - lekar lekar	Telefon: 08554555855, 55558888555
12	oblast: london, obshtina Qmbol, naseleno mqsto: gr. London
13	adres: nqkakyw 723
14	Upravitel: imeto na liceto I nqkoj drug
15	Telefon: 08554555855, 55558888555
16	1. dido mesoto hapvane - lekar lekar
Sheet1

Cell Formulas
Range	Formula
H2,H11	H2	=INDEX(B$2:B$16,MATCH("*"&G2,B$2:B$16,0)-1)

If that sample data was not truly representative, then give us some more that is (& include the expected results again).

 

[K0st4din]

Hello
yes, really this is only a small part of everything I try to do.
For this reason (since the tables are in a different language from English, I will make a copy in English) and I will explain what I am having trouble with.
For starters, these are the same / similar columns with a lot of ordered information. From this column I try to extract only the information I need and then in case of specific matches to arrange them in one line.
But so far the mission is very time consuming and I have entered several formulas for each column, and then I rearrange them to be in the same order.
I'm crazy to the Nth degree!

test name lek.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1				District	Locality	Address	General practitioner	Phone
2		2. joki moki ood		london city	gr. London city	nqkakyw si 23	Penko ivanov topalov - General practitioner	Phone: 08554555855, 55558888555
3		District: london city, obshtina Qmbol, Locality: gr. London city		london city	gr. London city	nqkakyw si 23	Drago Drasov Minev - General practitioner	Phone: 08554555855, 55558888555
4		Address: nqkakyw si 23		london city	gr. London city	nqkakyw si 23	Blago Ivanow Nqkoi - General practitioner	Phone: 08554555855, 55558888555
5		Upravitel: imeto na liceto I nqkoj drug		Veliko gradove	s. new jursey str	nqkakyw 723	dido mesoto hapvane - General practitioner	Phone: 08554555855, 55558888555
6		Phone: 08554555855, 55558888555		Shoumen	S.IVANOVO	4 Vasil Kolarov Str., Health service, kit and manipulation - address under Article 103 of the National Development Plan for MD 2015	MARIA IVANOVA RADUSHEVA - General practitioner	Phone: 0885159
7		1. Penko ivanov topalov - General practitioner		Shoumen	S.LOVETS	7 Kozloduy Street	MARIA IVANOVA RADUSHEVA - General practitioner	Phone: 0885159
8		2. Drago Drasov Minev - General practitioner		Shoumen	S.IVANOVO	Vasil Kolarov Str., Health service, kit and manipulation	MARIA IVANOVA RADUSHEVA - General practitioner	Phone: 0885159
9		3. Blago Ivanow Nqkoi - General practitioner		Shoumen	S.LOVETS	23 Svoboda Street, health service, manipulation room	MARIA IVANOVA RADUSHEVA - General practitioner	Phone: 0885159
10				Shoumen	VARBITSA	53 Septemvriysko Vastanie Str., 2nd floor, set 3 and manipulation	MARIA IVANOVA RADUSHEVA - General practitioner	Phone: 0885159
11		3. kukuruki duduruki ood		Shoumen	SHUMEN	100 Tsar Osvoboditel Str., 2nd floor, set 213, 3rd floor, 301, 302, 303, 304 and manipulation room 306, 100 Tsar Osvoboditel Str. 1, complex № 102, 103, 105 and manipulative № 104, 1A Lozengrad Str.	Albena Todorova Marinova - General Practitioner	Phone: 054 / 830,0886189
12		District: Veliko gradove, obshtina Qmbol, Locality: s. new jursey str		Shoumen	SHUMEN	100 Tsar Osvoboditel Str., 2nd floor, set 213, 3rd floor, 301, 302, 303, 304 and manipulation room 306, 100 Tsar Osvoboditel Str. 1, complex № 102, 103, 105 and manipulative № 104, 1A Lozengrad Str.	Veselin Krumov Valkov - General Practitioner	Phone: 054 / 830,0886189
13		Address: nqkakyw 723		Shoumen	SHUMEN	100 Tsar Osvoboditel Str., 2nd floor, set 213, 3rd floor, 301, 302, 303, 304 and manipulation room 306, 100 Tsar Osvoboditel Str. 1, complex № 102, 103, 105 and manipulative № 104, 1A Lozengrad Str.	Dilek Ekremova Hyusnieva - General Practitioner	Phone: 054 / 830,0886189
14		Upravitel: imeto na liceto I nqkoj drug		Shoumen	SHUMEN	100 Tsar Osvoboditel Str., 2nd floor, set 213, 3rd floor, 301, 302, 303, 304 and manipulation room 306, 100 Tsar Osvoboditel Str. 1, complex № 102, 103, 105 and manipulative № 104, 1A Lozengrad Str.	Emilia Yosifova Ilieva - General Practitioner	Phone: 054 / 830,0886189
15		Phone: 08554555855, 55558888555		Shoumen	SHUMEN	100 Tsar Osvoboditel Str., 2nd floor, set 213, 3rd floor, 301, 302, 303, 304 and manipulation room 306, 100 Tsar Osvoboditel Str. 1, complex № 102, 103, 105 and manipulative № 104, 1A Lozengrad Str.	Yanko Petrov Yankov - General Practitioner	Phone: 054 / 830,0886189
16		1. dido mesoto hapvane - General practitioner
17
18		1. AIP for PMP - Dr. MARIA IVANOVA RADUSHEVA
19		District: Shoumen, Municipality: VARBITSA, Locality: S.IVANOVO
20		Address: 4 Vasil Kolarov Str., Health service, kit and manipulation - address under Article 103 of the National Development Plan for MD 2015
21		District: Shoumen, Municipality: VARBITSA, Locality: S.LOVETS
22		Address: 7 Kozloduy Street
23		District: Shoumen, Municipality: VARBITSA, Locality: S.IVANOVO
24		Address: Васи4 Vasil Kolarov Str., Health service, kit and manipulation
25		District: Shoumen, Municipality: VARBITSA, Locality: S.LOVETS
26		Address: 23 Svoboda Street, health service, manipulation room
27		District: Shoumen, Municipality: VARBITSA, Locality: VARBITSA
28		Address: 53 Septemvriysko Vastanie Str., 2nd floor, set 3 and manipulation
29		Manager: MARIA IVANOVA RADUSHEVA
30		Phone: 0885159
31		1. MARIA IVANOVA RADUSHEVA - General practitioner
32
33		82. LK BIOSHANCE
34		District: Shoumen, Municipality: SHUMEN, Locality: SHUMEN
35		Address: 100 Tsar Osvoboditel Str., 2nd floor, set 213, 3rd floor, 301, 302, 303, 304 and manipulation room 306, 100 Tsar Osvoboditel Str. 1, complex № 102, 103, 105 and manipulative № 104, 1A Lozengrad Str.
36		Manager: VESELIN KRUMOV VALKOV
37		Phone: 054 / 830,0886189
38		1. Albena Todorova Marinova - General Practitioner
39		2. Veselin Krumov Valkov - General Practitioner
40		3. Dilek Ekremova Hyusnieva - General Practitioner
41		4. Emilia Yosifova Ilieva - General Practitioner
42		5. Yanko Petrov Yankov - General Practitioner
43
44
45
46
47
48
Sheet1

Actually, I extract in different columns with different formulas, then each information is shifted and from here on the rearrangement begins to reach the example shown and the actual tables. I was thinking about a macro, but I don't know how to push it to automate things.

 

[Peter_SSs]

OK, try this. Should work in all your versions except Excel 2007.

K0st4din.xlsm
	A	B	C				G	H
1							General practitioner
2		2. joki moki ood					Penko ivanov topalov - General practitioner	Phone: 08554555855, 55558888555
3		District: london city, obshtina Qmbol, Locality: gr. London city					Drago Drasov Minev - General practitioner	Phone: 08554555855, 55558888555
4		Address: nqkakyw si 23					Blago Ivanow Nqkoi - General practitioner	Phone: 08554555855, 55558888555
5		Upravitel: imeto na liceto I nqkoj drug					dido mesoto hapvane - General practitioner	Phone: 08554555855, 55558888555
6		Phone: 08554555855, 55558888555					MARIA IVANOVA RADUSHEVA - General practitioner	Phone: 0885159
7		1. Penko ivanov topalov - General practitioner					MARIA IVANOVA RADUSHEVA - General practitioner	Phone: 0885159
8		2. Drago Drasov Minev - General practitioner					MARIA IVANOVA RADUSHEVA - General practitioner	Phone: 0885159
9		3. Blago Ivanow Nqkoi - General practitioner					MARIA IVANOVA RADUSHEVA - General practitioner	Phone: 0885159
10							MARIA IVANOVA RADUSHEVA - General practitioner	Phone: 0885159
11		3. kukuruki duduruki ood					Albena Todorova Marinova - General Practitioner	Phone: 054 / 830,0886189
12		District: Veliko gradove, obshtina Qmbol, Locality: s. new jursey str					Veselin Krumov Valkov - General Practitioner	Phone: 054 / 830,0886189
13		Address: nqkakyw 723					Dilek Ekremova Hyusnieva - General Practitioner	Phone: 054 / 830,0886189
14		Upravitel: imeto na liceto I nqkoj drug					Emilia Yosifova Ilieva - General Practitioner	Phone: 054 / 830,0886189
15		Phone: 08554555855, 55558888555					Yanko Petrov Yankov - General Practitioner	Phone: 054 / 830,0886189
16		1. dido mesoto hapvane - General practitioner
17
18		1. AIP for PMP - Dr. MARIA IVANOVA RADUSHEVA
19		District: Shoumen, Municipality: VARBITSA, Locality: S.IVANOVO
20		Address: 4 Vasil Kolarov Str., Health service, kit and manipulation - address under Article 103 of the National Development Plan for MD 2015
21		District: Shoumen, Municipality: VARBITSA, Locality: S.LOVETS
22		Address: 7 Kozloduy Street
23		District: Shoumen, Municipality: VARBITSA, Locality: S.IVANOVO
24		Address: Васи4 Vasil Kolarov Str., Health service, kit and manipulation
25		District: Shoumen, Municipality: VARBITSA, Locality: S.LOVETS
26		Address: 23 Svoboda Street, health service, manipulation room
27		District: Shoumen, Municipality: VARBITSA, Locality: VARBITSA
28		Address: 53 Septemvriysko Vastanie Str., 2nd floor, set 3 and manipulation
29		Manager: MARIA IVANOVA RADUSHEVA
30		Phone: 0885159
31		1. MARIA IVANOVA RADUSHEVA - General practitioner
32
33		82. LK BIOSHANCE
34		District: Shoumen, Municipality: SHUMEN, Locality: SHUMEN
35		Address: 100 Tsar Osvoboditel Str., 2nd floor, set 213, 3rd floor, 301, 302, 303, 304 and manipulation room 306, 100 Tsar Osvoboditel Str. 1, complex № 102, 103, 105 and manipulative № 104, 1A Lozengrad Str.
36		Manager: VESELIN KRUMOV VALKOV
37		Phone: 054 / 830,0886189
38		1. Albena Todorova Marinova - General Practitioner
39		2. Veselin Krumov Valkov - General Practitioner
40		3. Dilek Ekremova Hyusnieva - General Practitioner
41		4. Emilia Yosifova Ilieva - General Practitioner
42		5. Yanko Petrov Yankov - General Practitioner
Sheet2

Cell Formulas
Range	Formula
H2:H15	H2	=INDEX(B:B,AGGREGATE(14,6,ROW(B$1:B$42)/((ROW(B$1:B$42)<MATCH("*"&G2,B:B,0))*(LEFT(B$1:B$42,6)="Phone:")),1))

 

[K0st4din]

Thank you very much, but unfortunately in the office, 2007 is used and if the formula explodes there, I will not be able to work and I will only have to work at home.
If you say, I will make a brand new query, but since the idea is absolutely the same, let me ask - from the results shown (data) in column B2:B, can something be done (even with a macro) to show the table with all the information on the right, ie District, Locality, Address, General practitioner, Phone

Thank you very much!