Look up code / formula

sjchurcher

New Member
Joined
Jul 20, 2017
Messages
4
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

DateExternalOtherOtherCo1Co2Co3Co4Co5Co6Co7Co8Co9Co10OtherOtherOtherOtherOther
01/04/17Name 1YRSNS

<tbody>
</tbody>
 

wideboydixon

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
You could certainly do that with VBA but there's also a simpler way if you're prepared to add an extra column:

<b>Excel 2016 (Windows) 32 bit</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th><th>S</th><th>T</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Date</td><td style="font-weight: bold;;">External</td><td style="font-weight: bold;;">Other</td><td style="font-weight: bold;;">Other</td><td style="font-weight: bold;;">Co1</td><td style="font-weight: bold;;">Co2</td><td style="font-weight: bold;;">Co3</td><td style="font-weight: bold;;">Co4</td><td style="font-weight: bold;;">Co5</td><td style="font-weight: bold;;">Co6</td><td style="font-weight: bold;;">Co7</td><td style="font-weight: bold;;">Co8</td><td style="font-weight: bold;;">Co9</td><td style="font-weight: bold;;">Co10</td><td style="font-weight: bold;;">Co11</td><td style="font-weight: bold;;">Co12</td><td style="font-weight: bold;;">Co13</td><td style="font-weight: bold;;">Co14</td><td style="font-weight: bold;;">Copy?</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">01/04/2017</td><td style=";">Name 1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Y</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">R</td><td style=";">S</td><td style=";">N</td><td style=";">S</td><td style="text-align: right;;">TRUE</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">T2</th><td style="text-align:left">=IFERROR(<font color="#0000FF">FIND(<font color="#FF0000">"Y",CONCAT(<font color="#00FF00">F2:S2</font>)</font>)>0,FALSE</font>)</td></tr></tbody></table></td></tr></table><br />

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

New Member
Joined
Jul 20, 2017
Messages
4
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

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
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

New Member
Joined
Jul 20, 2017
Messages
4
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

Well-known Member
Joined
Jun 2, 2016
Messages
3,401
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

New Member
Joined
Jul 20, 2017
Messages
4
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?
 

Forum statistics

Threads
1,081,418
Messages
5,358,559
Members
400,504
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top