Get instant live Excel expert help to learn how to use Search function in Excel?

I have a report template and I want to be able to link a raw data page to it so that whenever anyone inputs raw data it automatically is populated into the template and organized by project title. I've used If, Search, V lookup, and Isnumber functions and have not been able to get the desired effect. Ex. Page 1 Template with Column Labels: Purple, Blue, Red, Yellow Page 2 Raw Data List: Purple Shoes, Purple Hat, Blue Socks, Red Scarf, Blue Tie etc Page 3 Automatically populates with all the Purples under the Purple column, all the Blues under the Blue column etc. Formula Examples: =IF(ISNUMBER(SEARCH('Goal SLAs'!$E$8,'Raw Data'!$C:$C)),'Raw Data'!C2:C2,IF(ISNUMBER(SEARCH('Goal SLAs'!E5,'Raw Data'!C:C)),'Raw Data'!C3,"error")) =IF(ISNUMBER(SEARCH('Goal SLAs'!E18,'Raw Data'!C:C)),'Raw Data'!C12,"") =VLOOKUP('Goal SLAs'!E8,'Raw Data'!A1:H10,2,TRUE)

Im thinking I need to use an excel index match multiple results but I want it to search through multiple columns and return values that aren't 0

Hello, I am looking to write a macro for a command button that hides certain columns based on a drop down selection that contains the column headers. I have data from columns P:BE and for example the header in column P is "BEV01" so if I select "BEV01" from my drop down list, the command button will search that and if it says "BEV01" it will hide all other columns besides this. However when I go to write an elseif for the second column header "BEV02" I get an error "13 item does not match. So the code works for when BEV01 is selected in the drop down in cell K2 but not for when BEV02 is selected in K2. I am eventually going to continue this code for the entirety of the columns. See code below: what can i do to fix this? thanks Private Sub CommandButton1_Click() If Range("K2").Value = "BEV01" Then Columns("Q:BE").EntireColumn.Hidden = True ElseIf Range("K2").Value = "BEV02" Then Columns(":BE").EntireColumn.Hidded = True Else Columns("Q:BE").EntireColumn.Hidden = False End If End Sub

I need to excel to search a field of cells for a certain name and upon finding the name have a total that adds each finding together based each colum that has different numbers assigned to them.

Need code improvement help Want to specify a list of extensions to search for and not have to run code multiple times to grab them. Keep in mind the following instructions: 1. "Source" sheet is the file that contains your database 2. You wil have to add a blank sheet to workbook and name it Destination 3. .pdf and .xlsx and more are the extensions extracted to Destination using this code. 4. Run the code once, all .pdf extension files will be xtracted to Destination, then run the same code again, all .xlsx will be extracted to Destination below the .pdf's Sub Extraction() Dim xRg As Range Dim xCell As Range Dim I As Long Dim J As Long I = Worksheets("Source").UsedRange.Rows.Count J = Worksheets("Destination").UsedRange.Rows.Count If J = 1 Then If Application.WorksheetFunction.CountA(Worksheets("Destination").UsedRange) = 0 Then J = 0 End If Set xRg = Worksheets("Source").Range("F2:F" & I) On Error Resume Next Application.ScreenUpdating = False For Each xCell In xRg If CStr(xCell.Value) = ".pdf" Or CStr(xCell.Value) = ".xlsx" Or CStr(xCell.Value) = ".xls" Or CStr(xCell.Value) = ".doc" Or CStr(xCell.Value) = ".docx" Then xCell.EntireRow.Copy Destination:=Worksheets("Destination").Range("A" & J + 1) xCell.EntireRow.Delete J = J + 1 End If Next Application.ScreenUpdating = True End Sub

Keep in mind the following instructions: 1. "Source" sheet is the file that contains your database 2. You wil have to add a blank sheet to workbook and name it Destination 3. .pdf and .xlsx and more are the extensions extracted to Destination using this code. 4. Run the code once, all .pdf extension files will be xtracted to Destination, then run the same code again, all .xlsx will be extracted to Destination below the .pdf's Sub Extraction() Dim xRg As Range Dim xCell As Range Dim I As Long Dim J As Long I = Worksheets("Source").UsedRange.Rows.Count J = Worksheets("Destination").UsedRange.Rows.Count If J = 1 Then If Application.WorksheetFunction.CountA(Worksheets("Destination").UsedRange) = 0 Then J = 0 End If Set xRg = Worksheets("Source").Range("F2:F" & I) On Error Resume Next Application.ScreenUpdating = False For Each xCell In xRg If CStr(xCell.Value) = ".pdf" Or CStr(xCell.Value) = ".xlsx" Or CStr(xCell.Value) = ".xls" Or CStr(xCell.Value) = ".doc" Or CStr(xCell.Value) = ".docx" Then xCell.EntireRow.Copy Destination:=Worksheets("Destination").Range("A" & J + 1) xCell.EntireRow.Delete J = J + 1 End If Next Application.ScreenUpdating = True End Sub Code Snips for re-use To Search for Extensions in bulk Sub ConvertWordsToPdfs() Dim directory As String directory = "C:\Wordup" ' The starting directory Dim fso, newFile, folder, files Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(directory) Set files = folder.files For Each file In files Dim newName As String newName = Replace(file.Path, ".doc", ".pdf") Documents.Open FileName:=file.Path, _ ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _ PasswordDocument:="", PasswordTemplate:="", Revert:=False, _ WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _ wdOpenFormatAuto, XMLTransform:="" ActiveDocument.ExportAsFixedFormat OutputFileName:=newName, _ ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _ wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _ Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _ CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO19005_1:=False ActiveDocument.Close Next End Sub OPENING MULTIPLE FILES Sub OpenMultipleFiles() Dim Filter As String, Title As String, msg As String Dim i As Integer, FilterIndex As Integer Dim Filename As Variant ' File filters Filter = "Excel Files (*.xls),*.xls," & _ "Text Files (*.txt),*.txt," & _ "All Files (*.*),*.*" ' Default filter to *.* FilterIndex = 3 ' Set Dialog Caption Title = "Select File(s) to Open" ' Select Start Drive & Path ChDrive ("E") ChDir ("E:\Chapters\chap14") With Application ' Set File Name Array to selected Files (allow multiple) Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True) ' Reset Start Drive/Path ChDrive (Left(.DefaultFilePath, 1)) ChDir (.DefaultFilePath) End With ' Exit on Cancel If Not IsArray(Filename) Then MsgBox "No file was selected." Exit Sub End If ' Open Files For i = LBound(Filename) To UBound(Filename) msg = msg & Filename(i) & vbCrLf ' This can be removed Workbooks.Open Filename(i) Next i MsgBox msg, vbInformation, "Files Opened"' This can be removed End Sub

Need to improve existing code to accomodate multiple file extensions instead of just pdf xlsx etc... and to only run code once, instead of once per extension. My excel files are very large and I would like to set it to run and walk away so when i return it has finished searching for all extensions i specified and moved the lines to a new sheet. Here is the code and some snipits of code that may help. Keep in mind the following instructions: 1. "Source" sheet is the file that contains your database 2. You wil have to add a blank sheet to workbook and name it Destination 3. .pdf and .xlsx and more are the extensions extracted to Destination using this code. 4. Run the code once, all .pdf extension files will be xtracted to Destination, then run the same code again, all .xlsx will be extracted to Destination below the .pdf's Sub Extraction() Dim xRg As Range Dim xCell As Range Dim I As Long Dim J As Long I = Worksheets("Source").UsedRange.Rows.Count J = Worksheets("Destination").UsedRange.Rows.Count If J = 1 Then If Application.WorksheetFunction.CountA(Worksheets("Destination").UsedRange) = 0 Then J = 0 End If Set xRg = Worksheets("Source").Range("F2:F" & I) On Error Resume Next Application.ScreenUpdating = False For Each xCell In xRg If CStr(xCell.Value) = ".pdf" Or CStr(xCell.Value) = ".xlsx" Or CStr(xCell.Value) = ".xls" Or CStr(xCell.Value) = ".doc" Or CStr(xCell.Value) = ".docx" Then xCell.EntireRow.Copy Destination:=Worksheets("Destination").Range("A" & J + 1) xCell.EntireRow.Delete J = J + 1 End If Next Application.ScreenUpdating = True End Sub Code Snips for re-use To Search for Extensions in bulk Sub ConvertWordsToPdfs() Dim directory As String directory = "C:\Wordup" ' The starting directory Dim fso, newFile, folder, files Set fso = CreateObject("Scripting.FileSystemObject") Set folder = fso.GetFolder(directory) Set files = folder.files For Each file In files Dim newName As String newName = Replace(file.Path, ".doc", ".pdf") Documents.Open FileName:=file.Path, _ ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _ PasswordDocument:="", PasswordTemplate:="", Revert:=False, _ WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _ wdOpenFormatAuto, XMLTransform:="" ActiveDocument.ExportAsFixedFormat OutputFileName:=newName, _ ExportFormat:=wdExportFormatPDF, OpenAfterExport:=False, OptimizeFor:= _ wdExportOptimizeForPrint, Range:=wdExportAllDocument, From:=1, To:=1, _ Item:=wdExportDocumentContent, IncludeDocProps:=True, KeepIRM:=True, _ CreateBookmarks:=wdExportCreateNoBookmarks, DocStructureTags:=True, _ BitmapMissingFonts:=True, UseISO19005_1:=False ActiveDocument.Close Next End Sub OPENING MULTIPLE FILES Sub OpenMultipleFiles() Dim Filter As String, Title As String, msg As String Dim i As Integer, FilterIndex As Integer Dim Filename As Variant ' File filters Filter = "Excel Files (*.xls),*.xls," & _ "Text Files (*.txt),*.txt," & _ "All Files (*.*),*.*" ' Default filter to *.* FilterIndex = 3 ' Set Dialog Caption Title = "Select File(s) to Open" ' Select Start Drive & Path ChDrive ("E") ChDir ("E:\Chapters\chap14") With Application ' Set File Name Array to selected Files (allow multiple) Filename = .GetOpenFilename(Filter, FilterIndex, Title, , True) ' Reset Start Drive/Path ChDrive (Left(.DefaultFilePath, 1)) ChDir (.DefaultFilePath) End With ' Exit on Cancel If Not IsArray(Filename) Then MsgBox "No file was selected." Exit Sub End If ' Open Files For i = LBound(Filename) To UBound(Filename) msg = msg & Filename(i) & vbCrLf ' This can be removed Workbooks.Open Filename(i) Next i MsgBox msg, vbInformation, "Files Opened"' This can be removed End Sub

i need to be able to search for a postcode which then will create an outcome in another cell?

Column Q: MSKU – String representing a product Goal: Count number of days a row item in column Q was out of stock between 7/1/17 and 7/31/17 and pull that number into the row corresponding with that item in Column S Reference Array: The table Columns D:L represents a report in which each row a daily snapshot is given of a SKU in column F, the warehouse code representing the warehouse in which it is in stock in Column H, the quantity within that warehouse in Column I, and the date from which the data was pulled in Column L. Problem: Because my items may be in stock in multiple warehouses on any given date, it is challenging to create a formula to count the number of different dates in which an item had stock in any warehouse, as one SKU may appear multiple times in the column corresponding to the same date. The reports used to create the table Column D:L do not show line items without stock – They only have information on products that had stock on the dates recorded. So I cannot use a lookup formula to find dates in which stock was zero, because those dates would not be listed in the column. It will be a challenge but what I am looking for is a formula is column S that, using the line item in column R, will search the Table (Columns D:L) and find the number of dates between 7/1/17 and 7/31/17 in which no data could be found for the the line item in Column R

Column Q: MSKU – String representing a product Goal: Count number of days a row item in column Q was out of stock between 7/1/17 and 7/31/17 and pull that number into the row corresponding with that item in Column S Reference Array: The table Columns D:L represents a report in which each row a daily snapshot is given of a SKU in column F, the warehouse code representing the warehouse in which it is in stock in Column H, the quantity within that warehouse in Column I, and the date from which the data was pulled in Column L. Problem: Because my items may be in stock in multiple warehouses on any given date, it is challenging to create a formula to count the number of different dates in which an item had stock in any warehouse, as one SKU may appear multiple times in the column corresponding to the same date. The reports used to create the table Column D:L do not show line items without stock – They only have information on products that had stock on the dates recorded. So I cannot use a lookup formula to find dates in which stock was zero, because those dates would not be listed in the column. It will be a challenge but what I am looking for is a formula is column S that, using the line item in column R, will search the Table (Columns D:L) and find the number of dates between 7/1/17 and 7/31/17 in which no data could be found for the the line item in Column R