Search entire spreadsheet for Value, Return TRUE or FALSE

[fungiblecommodity]

<style type="text/css">P { margin-bottom: 0.21cm; }</style> Hi guys,

I am trying to figure out a formula that will allow me to search for a particular value across spreadsheets without knowing which column they are in.

I have a lot of data on customers from different sources, and have them in separate spreadsheets depending on where the data has come from. The spreadsheets are called "Main, Facebook, Quote, Pixel, Website".

What I want to do is copy and paste new data into "Main" sheet and have a column each for Facebook, Quote, Pixel and Website that checks whether that person's email address already exists in those sheets. Unfortunately the "email" column for each is in a different column, which is the trouble I ran into using Match and VLOOKUP. So what I'd like to do is search each entire spreadsheet for the email, and then return TRUE if Excel finds it. To be honest it doesn't have to return TRUE, it could return anything that would help me differentiate.

My main spreadsheet looks kind of like this:


<style>table { }td { padding-top: 1px; padding-right: 1px; padding-left: 1px; color: black; font-size: 12pt; font-weight: 400; font-style: normal; text-decoration: none; font-family: Calibri,sans-serif; vertical-align: bottom; border: medium none; white-space: nowrap; }.xl65 { border: 0.5pt solid windowtext; }.xl66 { color: rgb(5, 99, 193); text-decoration: underline; border: 0.5pt solid windowtext; }.xl67 { border: 0.5pt solid windowtext; }</style>

	A	B	C	D	E	F	G	H	I
	id	email	mobile_number	suburb	number	Facebook_Match	Quote_Match	Pixel_Match	Website_Match
1	5153	12312321@blah.com	28282414	MOUNT WAVERLEY	22222
2	7293	ajksdhfaskldfhjasdjkf3883@yahoo.com	212990323		33323
3	8183	kbaez@blah.org	6767676767	Swan Hill	3222585
4	12726	blahblah@yahoo.com.au	0986754977	KENSINGTON	3034331
5	14600	ge12ff@adsfjasdpfasdf.org	76849 737		34
6	24010	fjfjfjfj@dkdkd.com	36291739	Ringwood	3434134
7	27215	fjfadfasdfjfjfj@dkdkd.com	283746464	north melbourne	44444
8	28003	fjfjadfasdfasdffjfj@dkdkd.com	5678987		43233
9	34973	fjfjfasdfasdfasdfasdfjfj@dkdkd.com	2222222	Randwick	32222
10	37814	blah@dkdkd.com	33333232322	Kiama	222222

<tbody>
</tbody>

<style type="text/css">P { margin-bottom: 0.21cm; }</style> The closest I've got is using the formula on this page: http://www.mrexcel.com/forum/excel-...e-spreadsheet-value-return-cell-location.html and expanding it for my situation.

This is what I'm using in F2 at the moment, and it is returning $B$5677 for example when it finds the email value, and #VALUE when it is not.

=ADDRESS(MIN(IF('Facebook'!$A$1:$Z$6000=B2,ROW('Facebook'!$A$1:$Z$6000))),MIN(IF('Facebook'!$A$1:$Z$6000=B2,COLUMN('Facebook'!$A$1:$Z$6000))))

This would probably be fine, except that my Facebook spreadsheet has more than 17000 rows, and when I expand the range to anything above around $A$1:$Z$8500 it creates a #NAME? error which says "The formula contains unrecognized text". I'm assuming it has a maximum row number that I'm exceeding, but not sure.

I guess I have two questions:

1) Is there an easier way to do what I'm trying to do (ie. look up a cell's value in a whole spreadsheet without specifying a row or column)?

and 2) Why doesn't it work to simply put a larger row number in the formula above?

I have no experience with VBA but I'd be up for having a crack at it if there's no easy formula-based solution.

Any help would be much appreciated.

Thanks!

 

[WarPigl3t]

Start with an empty email column in your Main sheet. This macro will fill it out for you.

Sub runThisMacro()
     outputSheet = "Main"
     allTheSheets = allTheSheets_Function()
     idNumMain_Column = 1
     emailMain_Column = 2
     firstRow = 2
     lastRow = Cells(Rows.Count, idNumMain_Column).End(xlUp).Row
     r = firstRow
     Do Until r > lastRow
          searchID = Sheets(outputSheet).Cells(r, idNumMain_Column).Value
          For Each wksht In allTheSheets
               wkshtID = findTheRightColumn_Function(wksht, 1, "id")
               wkshtEmail = findTheRightColumn_Function(wksht, 1, "email")
               emailAddress = searchSheet_Function(wksht, wkshtID, wkshtEmail, searchID, firstRow)
               If emailAddress <> "" Then
                    Sheets(outputSheet).Cells(r, emailMain_Column).Value = emailAddress
                    Exit For
               End If
          Next wksht
          r = r + 1
     Loop
End Sub

Function searchSheet_Function(wksht, wkshtID, wkshtEmail, searchID, firstRow)
     lastRow = Sheets(wksht).Cells(Rows.Count, wkshtID).End(xlUp).Row
     r = firstRow
     Do Until r > lastRow
          myValue = Sheets(wksht).Cells(r, wkshtID).Value
          If myValue = searchID Then
               emailAddress = Sheets(wksht).Cells(r, wkshtEmail).Value
               searchSheet_Function = emailAddress
          End If
          r = r + 1
     Loop
     searchSheet_Function = ""
End Function

Function findTheRightColumn_Function(sheetName, rowSearch, stringSearch)
     lastColumn = Sheets(sheetName).Cells(rowSearch, Columns.Count).End(xlToLeft).Column
     c = 1
     Do Until c > lastColumn
          myValue = Sheets(sheetName).Cells(rowSearch, c).Value
          If LCase(myValue) = LCase(search string) Then
               findTheRightColumn_Function = c
               ExitFunction
          End If
          c = c + 1
     Loop
End Function

Function allTheSheets_Function()
     Dim myArray() As String
     ReDim Preserve myArray(3)
     myArray(0) = "Facebook"
     myArray(1) = "Quote"
     myArray(2) = "Pixel"
     myArray(3) = Website"
     allTheSheets_Function = myArray()
End Function

 

[fungiblecommodity]

Thanks for your response - I had a go at using that code for a macro but got an error:

Run-time error '9':
Subscript out of range

I hit debug and it highlighted this line with a yellow background:

searchID = Sheets(outputSheet).Cells(r, idNumMain_Column).Value

and this one was highlighted with red text:

If LCase(myValue) = LCase(search string) Then


Any suggestions on where it's going wrong?

 

[WarPigl3t]

Is the Main worksheet called "Main"? If not, then I don't know what's wrong with the searchID line.

The other line was an auto correct error because of my tablet. Here is the right code.
If LCase(myValue) = LCase(searchString) Then

 

[Peter_SSs]

If you only want to know if the email address appears on the sheet somewhere, then would this suffice?
I've put a range limit for each sheet of A1:Z30000
You need this to be big enough but don't go overboard as the bigger you go the more resources will be consumed.
The ranges on each sheet do not need to be the same so if, say, the Pixel sheet does not have alot of data, the range in that formula might be, say, $A$1:$T$1000

Note that I've hidden some of the columns. Each formula is copied down.

Excel Workbook
	B	F	G	H	I
1	email	Facebook_Match	Quote_Match	Pixel_Match	Website_Match
2	12312321@blah.com	TRUE	FALSE	FALSE	FALSE
3	ajksdhfaskldfhjasdjkf3883@yahoo.com	TRUE	TRUE	FALSE	TRUE
4	kbaez@blah.org	FALSE	FALSE	FALSE	FALSE
5	blahblah@yahoo.com.au	FALSE	FALSE	FALSE	FALSE
6	ge12ff@adsfjasdpfasdf.org	FALSE	FALSE	FALSE	FALSE
7	fjfjfjfj@dkdkd.com	TRUE	FALSE	TRUE	TRUE
8	fjfadfasdfjfjfj@dkdkd.com	TRUE	FALSE	TRUE	TRUE
9	fjfjadfasdfasdffjfj@dkdkd.com	TRUE	FALSE	TRUE	TRUE
10	fjfjfasdfasdfasdfasdfjfj@dkdkd.com	TRUE	FALSE	TRUE	FALSE
11	blah@dkdkd.com	TRUE	FALSE	TRUE	TRUE
Main

 

[fungiblecommodity]

Thanks very much Peter, that solution works perfectly for what I need. Appreciate the help, all

 

[Peter_SSs]

Thanks very much Peter, that solution works perfectly for what I need. Appreciate the help, all

You are welcome, glad to help. Simple is often the best.