Help With Search In Between Worksheets

[RYANE]

Hello,

I have a question that I hope someone can help me with. I have a workbook that contains a sheet called "Foundation Permits" that my company uses to track addresses of new building permits and whether or not we gained that address as a client. Sometimes we end up gaining projects that are not listed in "Foundation Permits" so we track them on a separate sheet named "No Permit SFDS". This list becomes very large quickly and has to be cross-referenced against the "Foundation Permits" regularly (which is quite time consuming.)

My goal is to find a way to have Excel grab each address that is in "No Permit SFDS" and check if it is listed in "Foundation Permits". If it is, I would like it to add "GAIN" in a separate column on the "Foundation Permits" and ideally remove the address from "No Permit SFDS". I'm aware this is quite complex and that is why I'm seeking help. If you have any information that can help me solve this problem I'd really appreciate it.

 

[svendiamond]

First hint for the forum: fewer words, more cell references. You will get an answer faster if we can easily visualize your data.

You can definitely achieve what you have explained here. It's not that complicated. Let's do the "GAIN" part first. Here:

Book1
	A	B
1	ADDRESS	LISTED?
2	123 MAIN ST	GAIN
3	540 JOLLY LANE
4	321 GREEN WAY	GAIN
No Permit SFDS

Cell Formulas
Range	Formula
B2	=IF(ISNUMBER(MATCH(A2,'Foundation Permits'!A:A,0)),"GAIN","")

 

[svendiamond]

This is assuming that the addresses on the "Foundation Permits" page are in column A. Which looks like this in my example:

Book1
	A
1	ADDRESS
2	123 MAIN ST
3	666 DEVIL AVE
4	321 GREEN WAY
Foundation Permits

 

[RYANE]

Thank you very much for that information! I played around with it a bit (probably because I didn't explain with cells and so on) but I got it to work. Here is the augmented formula I used:

=IF(ISNUMBER(MATCH(I:I,'NO PERMIT SFDS'!I:I,0)),"GAIN","")

I added this to all blank cells within column W in the Foundation Permits sheet. It now checks the addresses in Non Permit SFDS and adds GAIN if they are present!

So do happen to know how to get Excel to remove entries if they are found?

First hint for the forum: fewer words, more cell references. You will get an answer faster if we can easily visualize your data.

You can definitely achieve what you have explained here. It's not that complicated. Let's do the "GAIN" part first. Here:

	A	B
123 MAIN ST	GAIN
540 JOLLY LANE
321 GREEN WAY	GAIN

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="bgcolor: #F8CBAD"]ADDRESS[/TD]
[TD="bgcolor: #F8CBAD"]LISTED?[/TD]

[TD="align: center"]2[/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

</tbody>

No Permit SFDS​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=IF(ISNUMBER(MATCH(A2,'Foundation Permits'!A:A,0)),"GAIN","")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[svendiamond]

So do happen to know how to get Excel to remove entries if they are found?

Can you rephrase that question or add more details? What exactly is an "entry" and what do you mean if they "are found"? Do you mean removing rows where "GAIN" is found in column B? Do yourself a favor and get the Mr Excel HTML Maker from this link: http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2545970 - follow the instructions and use the add-in to copy and paste small snippets/screenshots of your workbook into the forum. That's what I used in my previous posts in this thread. You will have a much greater chance of getting a solution rather than trying to explain your issues with only words.

 

[RYANE]

The first goal was to have code that searched addresses in "NO PERMIT SFDS" in column I against the addresses in FOUNDATION PERMITS, then add "RMC" to the SUPPLIERS (FDNs) in column W if there was a match. I have accomplished this with you help by adding the code provided above into each cell in column W.

Now I would like to have code that removes the matched entries from NO PERMIT SFD. I hope I'm being clear here and thanks again for your help!

[B]NO PERMIT SFD[/B][TABLE]
<tbody>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]A[/TH]
[TH]C[/TH]
[TH]I[/TH]
[TH]S[/TH]
[TH]W[/TH]
[TH]X[/TH]
[TH]Y[/TH]
[TH]AA[/TH]
[TH]AB[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00, align: center"]NEW
INFO?[/TD]
[TD="bgcolor: #DBE5F1, align: center"]REGION[/TD]
[TD="bgcolor: #DBE5F1, align: center"]PROJECT ADDRESS[/TD]
[TD="bgcolor: #DBE5F1, align: center"]JOB STATUS[/TD]
[TD="bgcolor: #DBE5F1, align: center"]SUPPLIER
(FDNs)[/TD]
[TD="bgcolor: #DBE5F1, align: center"]RMC
PRICE[/TD]
[TD="bgcolor: #DBE5F1, align: center"]REP[/TD]
[TD="bgcolor: #DBE5F1, align: center"]NOTES[/TD]
[TD="bgcolor: #DBE5F1, align: center"]Last
Visited[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Vancouver[/TD]
[TD="align: center"]3685 W 3 Ave[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"] $ 135.00[/TD]
[TD="align: center"]MK[/TD]
[TD="align: center"]Nov 27th 2015[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]NO PERMIT SFDS[/B][/COLOR][/CENTER]

[B]FOUNDATION PERMITS[/B][TABLE]
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>[TR="bgcolor: #E0E0F0"]
[TH][/TH]
[TH]A[/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]K[/TH]
[TH]M[/TH]
[TH]Q[/TH]
[TH]S[/TH]
[TH]T[/TH]
[TH]U[/TH]
[TH]V[/TH]
[TH]W[/TH]
[TH]X[/TH]
[TH]Y[/TH]
[TH]Z[/TH]
[TH]AA[/TH]
[TH]AB[/TH]
[/TR]
</thead><tbody>[TR]
[TD="align: center"]1[/TD]
[TD="bgcolor: #FFFF00, align: center"]NEW
INFO?[/TD]
[TD="bgcolor: #EAF1DD, align: center"]ISSUE
DATE[/TD]
[TD="bgcolor: #EAF1DD, align: center"]REGION[/TD]
[TD="bgcolor: #EAF1DD, align: center"]OWNER[/TD]
[TD="bgcolor: #EAF1DD, align: center"]OWNER ADDRESS[/TD]
[TD="bgcolor: #EAF1DD, align: center"]OWNER CITY[/TD]
[TD="bgcolor: #EAF1DD, align: center"]OWNER POSTAL CODE[/TD]
[TD="bgcolor: #EAF1DD, align: center"]OWNER PHONE NUMBER[/TD]
[TD="bgcolor: #EAF1DD, align: center"]PROJECT ADDRESS[/TD]
[TD="bgcolor: #EAF1DD, align: center"]PROJECT TYPE[/TD]
[TD="bgcolor: #EAF1DD, align: center"]CONTRACTOR[/TD]
[TD="bgcolor: #EAF1DD, align: center"]CONTRACTOR
PHONE NUMBER[/TD]
[TD="bgcolor: #EAF1DD, align: center"]JOB STATUS[/TD]
[TD="bgcolor: #EAF1DD, align: center"]ACCOUNT NO.[/TD]
[TD="bgcolor: #EAF1DD, align: center"]VISA/MC[/TD]
[TD="bgcolor: #EAF1DD, align: center"]PLACER[/TD]
[TD="bgcolor: #EAF1DD, align: center"]SUPPLIER
(FDNs)[/TD]
[TD="bgcolor: #EAF1DD, align: center"]RMC
PRICE[/TD]
[TD="bgcolor: #EAF1DD, align: center"]REP[/TD]
[TD="bgcolor: #EAF1DD, align: center"]OTHER
PRICE[/TD]
[TD="bgcolor: #EAF1DD, align: center"]NOTES[/TD]
[TD="bgcolor: #EAF1DD, align: center"]Last
Visited[/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][COLOR=#ff0000][B]Jan 06[/B][/COLOR][/TD]
[TD="align: center"][COLOR=#ff0000][B]Vancouver[/B][/COLOR][/TD]
[TD="align: center"][COLOR=#ff0000][B]Surinder Sidhu[/B][/COLOR][/TD]
[TD="align: center"][/TD]
[TD="align: center"][COLOR=#ff0000][B]Vancouver[/B][/COLOR][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][COLOR=#ff0000][B]3685 W 3 Ave[/B][/COLOR][/TD]
[TD="align: center"][COLOR=#ff0000][B]SFD[/B][/COLOR][/TD]
[TD="align: center"][COLOR=#ff0000][B]Super Quality Homes Ltd[/B][/COLOR][/TD]
[TD="align: center"][COLOR=#ff0000][B]604-507-7541[/B][/COLOR][/TD]
[TD="bgcolor: #FFFFFF, align: center"][COLOR=#ff0000][B]POUR[/B][/COLOR][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][COLOR=#ff0000][B]RMC[/B][/COLOR][/TD]
[TD="bgcolor: #FFFFFF, align: center"][COLOR=#ff0000][B]$135.00[/B][/COLOR][/TD]
[TD="bgcolor: #FFFFFF, align: center"][COLOR=#ff0000][B] MK [/B][/COLOR][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][COLOR=#ff0000][B]POURED JAN 20 2016[/B][/COLOR][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jan 06[/TD]
[TD="align: center"]Burnaby[/TD]
[TD="align: center"]Irene Lin[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Vancouver[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4655 Napier St[/TD]
[TD="align: center"]SFD[/TD]
[TD="align: center"]Yue Xong Constr[/TD]
[TD="align: center"]604-780-3018[/TD]
[TD="bgcolor: #FFFFFF, align: center"]POUR[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]RMC[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] MK [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]Pouring Thrusday March 24th, [/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jan 06[/TD]
[TD="align: center"]Burnaby[/TD]
[TD="align: center"]Wendy Gee[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Vancouver[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4738 Maitland St[/TD]
[TD="align: center"]SFD[/TD]
[TD="align: center"]Bright Coast Homes Ltd[/TD]
[TD="align: center"]604-356-0199[/TD]
[TD="bgcolor: #FFFFFF, align: center"]POUR[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]RMC[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"] MK [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]Poured Feb 26 2016?[/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Jan 06[/TD]
[TD="align: center"]Burnaby[/TD]
[TD="align: center"]Brian Tsang[/TD]
[TD="align: center"][/TD]
[TD="align: center"]Vancouver[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4757 Gilpin Ct[/TD]
[TD="align: center"]SFD[/TD]
[TD="align: center"]Enrich Dev Ltd[/TD]
[TD="align: center"]604-779-8436[/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]RMC[/TD]
[TD="bgcolor: #FFFFFF, align: center"]$135.00[/TD]
[TD="bgcolor: #FFFFFF, align: center"] JE [/TD]
[TD="bgcolor: #FFFFFF, align: center"][/TD]
[TD="bgcolor: #FFFFFF, align: center"]Poured Jan 29 2016[/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]
[CENTER][COLOR=#161120][B]FOUNDATION PERMITS[/B][/COLOR][/CENTER]