Look up code / formula

[sjchurcher]

Hi all,

First time poster here - site was recommended to me!

So, I have a log of hundreds of contracts which is set up with the external company name in column C, then all of our company names in columns F - S. The contract is logged by a Y being entered in to the column that related to the name of our company which has signed the agreement with the external party. (Tried to show below)

What I need to be able to do is have excel look for a Y in columns that relate only to one of our companies (for example column K) then copy the data in that row to another spreadsheet.

I've been told this may need coding rather than a formula, can anyone help?

Thank you

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]Date[/TD]
[TD]External[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[TD]Co1[/TD]
[TD]Co2[/TD]
[TD]Co3[/TD]
[TD]Co4[/TD]
[TD]Co5[/TD]
[TD]Co6[/TD]
[TD]Co7[/TD]
[TD]Co8[/TD]
[TD]Co9[/TD]
[TD]Co10[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[TD]Other[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]01/04/17[/TD]
[TD]Name 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Y[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]R[/TD]
[TD]S[/TD]
[TD]N[/TD]
[TD]S[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[wideboydixon]

You could certainly do that with VBA but there's also a simpler way if you're prepared to add an extra column:

Excel 2016 (Windows) 32 bit
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	Date	External	Other	Other	Co1	Co2	Co3	Co4	Co5	Co6	Co7	Co8	Co9	Co10	Co11	Co12	Co13	Co14	Copy?
2	01/04/2017	Name 1								Y					R	S	N	S	TRUE
Sheet1

Cell Formulas
Range	Formula
T2	=IFERROR(FIND("Y",CONCAT(F2:S2))>0,FALSE)

Column T then indicates whether this row should be copied. You can then apply a filter to the data where column T is "TRUE" and simply copy/paste the visible rows.

WBD

 

[sjchurcher]

Thank you - the problem I have is it can't involved a manual copy and paste because it's not only me that uses the spreadsheet and there are some members of the team who I know wouldn't copy and paste. Trying to automate it as far as possible

 

[wideboydixon]

OK. They'll still need to invoke the macro though; how are you planning to get them to do that? Are the values copied to a new sheet in the same workbook or a different workbook? Do you want to use an existing sheet/workbook or create a new one?

WBD

 

[sjchurcher]

I can manage a macro - I can run it daily if I ever think it hasn't been updated.

It will be copied in to a new sheet in the same workbook, I have one created with basic headers etc, but can be deleted if easier to create from new

 

[wideboydixon]

OK. It's a simple macro. Some final questions so I get it right:

1. What's the name of the sheet you're copying from?
2. What's the name of the sheet you're copying to?
3. Do you want to copy the entire row? If not, what columns?
4. Once copied, what do you want to do with the row to prevent it from being copied again?

WBD

 

[sjchurcher]

thank you.

1. NDAs
2. PLC NDAs
3. Columns B,C,D,E,T,U,V,W,X
4. Ideally the row needs to stay in the original document - can it be locked? Or what other options might there be?