Listing text mentions in Excel

[rmilos]

I have a project where I need to match my company's disciplines with the previous client projects.

I have sheet one where I will list a client name in column A1 with disciplines used for that client in B1, C1, D1, etc.
Client two in A2 with discipline in B2, C2, etc.

Sheet two contains the disciplines in A1, B1, C1, D1, etc.

Is there a formula the hat would return a list of client names with that discipline on sheet 1 under the discipline heading?

 

[Eric W]

Roughly how many clients do you have, and how many disciplines do you have? Are you averse to using helper columns?

 

[rmilos]

Over 100 with six disciplines

I've cobbled this: =ADDRESS(MATCH("*POS*",[Sheet1]Projects!D3:D100,0)+ROW([Sheet]Projects!D3)-1,COLUMN([Sheet1]Projects!D3:D100),4)

but only get one row returned.


Sheet 1

Client	Disciplines
Adidas	Cabling	Maintenance	POS
BB	Cabling			Testing
CC	Cabling		POS
	Cabling
					Telecom
						Other

<colgroup><col><col><col><col span="8"></colgroup><tbody>
</tbody>

Sheet 2

Cabling	Maintenance	POS	Testing	Tele-com	Other
B3	C3	D3	E4	F7	G8

<colgroup><col><col><col><col><col span="4"></colgroup><tbody>
</tbody>

 

[Eric W]

You actually made it easier by requiring that all the disciplines on Sheet1 remain in designated columns (Cabling in column B, Maintenance in column C, etc.). I worried that you were just planning on putting them in adjacent cells. Given that, enter this formula on Sheet2, cell A2:

=IFERROR(INDEX(Projects!$A$1:$A$200,SMALL(IF(Projects!B$1:B$200<>"",ROW(B$1:B$200),0),ROW()+COUNTIF(Projects!B$1:B$200,"=")-1)),"")

This is an array formula, so use Control-Shift-Enter when putting it in the formula bar. Then copy it to A2:F200.

Let me know how it works.

 

[rmilos]

You actually made it easier by requiring that all the disciplines on Sheet1 remain in designated columns (Cabling in column B, Maintenance in column C, etc.). I worried that you were just planning on putting them in adjacent cells. Given that, enter this formula on Sheet2, cell A2:

=IFERROR(INDEX(Projects!$A$1:$A$200,SMALL(IF(Projects!B$1:B$200<>"",ROW(B$1:B$200),0),ROW()+COUNTIF(Projects!B$1:B$200,"=")-1)),"")

This is an array formula, so use Control-Shift-Enter when putting it in the formula bar. Then copy it to A2:F200.

Let me know how it works.

This provides only one "True" with no location from sheet 1

A B C
Cabling Maintenance POS

True C3 D3

 

[Eric W]

I'm not quite sure what to tell you. Here's a copy of my test sheets:

	A	B	C	D	E	F	G
1	one	Cabling	Maintenance	POS	Testing		other
2	two	Cabling
3	three		Maintenance			Telecom
4	four	Cabling			testing

<tbody>
</tbody>

	A	B	C	D	E	F
1	Cabling	Maintenance	POS	Testing	Telecom	other
2	one	one	one	one	three	one
3	two	three		four
4	four

<tbody>
</tbody>

Are your cell ranges the same as this? Did you remember to confirm the formula with Control-Shift-Enter? Does this represent your expected results?

Let me know.

 

[rmilos]

When I add your formula to Sheet 2, A2 this is what I get "Client" on sheet 2.

	A	B	C	D	E	F	G
1	Client	Disciplines
2
3	Adidas	Cabling	Maintenance	POS
4	BB	Cabling			Testing
5	CC	Cabling		POS
6	DD	Cabling
7	EE						Other
8	FF					Tele-com

<tbody>
</tbody>

Sheet 1

Sheet 2

	A	B	C	D	E	F	G
1	Cabling	Maintenance	POS	Testing	Tele-com	Other
2	Client	D3	C3	E4	F7	G8
3
4
5
6
7

<tbody>
</tbody>

I'm not smart in all this - Sorry

 

[Eric W]

No problem - you learn by asking.

The problem is that I did not realize that you had a header row on the client sheet. I had to adjust the formula to account for that. Try this:

=IFERROR(INDEX(Projects!$A$3:$A$202,SMALL(IF(Projects!B$3:B$202<>"",ROW(B$1:B$200),0),ROW()+COUNTIF(Projects!B$3:B$202,"=")-1)),"")

Put that in A2 on Sheet2 and copy it to the other cells.

Let me know if that works.

 

[rmilos]

The good part is that it retrieves the name of the company not the cell name! The bad news is that it only returns on name and returns that one name in all discipline cells even though the discipline is not listed for that company (Adidas)

Cabling	Maintenance	POS	Testing	Tele-com	Other
Adidas	Adidas	Adidas	Adidas	F7	G8

<tbody>
</tbody>

 

[Eric W]

Well, 2 steps forward, 1 step back I guess. We just have to figure out how your spreadsheet differs from my test spreadsheet.

When you copied the formula from cell A2, did you select cell A2, copy it with Control-C (or right-click on the cell and select Copy, or the button on the ribbon), then select B2:D2, and paste it with Control-V (or right-click on the cell and select Paste)?

Or did you go to cell D2 and paste the formula in the formula bar?

Those 2 methods have very different results. Try using the first method, and do a few rows and let me know what happens.