Fill in data depending on header name

[Cruiser69]

Hi all

I was wondering if anyone can help.
A vendor payment doc is created weekly to pay out vendors
Is there any way to auto fill the Comments column (F) to show multiple sale ID's as in the second example in the image attached.
This is just an example sheet as there can be up to 9 sales per week with up to 50 different vendors in each sale.
It gets very time consuming filling each comment cell with the same vendor in multiple sales
Any cell with data to reference the column header and any blank cells, just leave blank.


Thanks for looking

Regards,

Graham

 

[Fluff]

I suggest that you update your Account details (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

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.

 

[Cruiser69]

Thanks Fluff.

I think I have added it correctly

Vendor payment temp.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	SALE I.D	VENDOR REF	Pay Y/N	NOT SOLD	NEW REF	COMMENTS	SALE04	SALE05	SALE06	SALE07	SALE08
2	SALE01	14051	N				14051
3	SALE01	AMAZ	N					AMAZ
4	SALE01	CJW	Y	1	CJW1
5	SALE01	COU	N							COU	COU
6	SALE01	DOC	Y
7
8	SALE02	JL1	N					JL1	JL1	JL1
9	SALE02	EE1	N				EE1
10	SALE02	ST2	N				ST2			ST2
11	SALE02	TUV	Y
12	SALE02	WA2	Y
13
14
15
16						How it should look
17
18	SALE I.D	VENDOR REF	Pay Y/N	NOT SOLD	NEW REF	COMMENTS	SALE04	SALE05	SALE06	SALE07	SALE08
19	SALE01	14051	N			More in SALE04	14051
20	SALE01	AMAZ	N			More in SALE05		AMAZ
21	SALE01	CJW	Y	1	CJW1
22	SALE01	COU	N			More in SALE07, SALE08				COU	COU
23	SALE01	DOC	Y
24
25	SALE02	JL1	N			More in SALE05, SALE06, SALE07		JL1	JL1	JL1
26	SALE02	EE1	N			More in SALE04	EE1
27	SALE02	ST2	N			More in SALE04, SALE07	ST2			ST2
28	SALE02	TUV	Y
29	SALE02	WA2	Y
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
K10	Cell	does not contain a blank value	text	NO
K8:K9	Cell	does not contain a blank value	text	NO
I27	Cell	does not contain a blank value	text	NO
G19:J26,J27,G27:H27,G28:J29,K19:K29	Cell	does not contain a blank value	text	NO
C18:C29	Other Type	Color scale		NO
I10	Cell	does not contain a blank value	text	NO
G2:J9,J10,G10:H10,K2:K7,G11:K12	Cell	does not contain a blank value	text	NO
C1:C12	Other Type	Color scale		NO

Regards,

Graham

 

[Fluff]

Thanks for that & for updating your profile.
As you are using 2016 you don't have the textjoin function, so a UDF would probably be easier, is that ok?

 

[Cruiser69]

Hi Fluff.
That is fine

Regards,

Graham

 

[Cruiser69]

Sorry Fluff

It is 365 I am using.
I've got 2016 at home

 

[Fluff]

In that case, how about

+Fluff v2.xlsm
	A	B	C	D	E	F	G	H	I	J	K
1	SALE I.D	VENDOR REF	Pay Y/N	NOT SOLD	NEW REF	COMMENTS	SALE04	SALE05	SALE06	SALE07	SALE08
2	SALE01	14051	N			More in SALE04	14051
3	SALE01	AMAZ	N			More in SALE05		AMAZ
4	SALE01	CJW	Y	1	CJW1
5	SALE01	COU	N			More in SALE07, SALE08				COU	COU
6	SALE01	DOC	Y
7
8	SALE02	JL1	N			More in SALE05, SALE06, SALE07		JL1	JL1	JL1
9	SALE02	EE1	N			More in SALE04	EE1
10	SALE02	ST2	N			More in SALE04, SALE07	ST2			ST2
11	SALE02	TUV	Y
12	SALE02	WA2	Y
Main

Cell Formulas
Range	Formula
F2:F12	F2	=IF(COUNTA(G2:K2),"More in "&TEXTJOIN(", ",1,FILTER($G$1:$K$1,G2:K2<>"","")),"")

 

[Cruiser69]

Thanks Fluff, that is spot on

Regards,

Graham

 

[Fluff]

You're welcome & thanks for the feedback.