Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Lookup and list all populated cells...

While I know this could be accomplished simply using a filter, I'd like to try to add a more user-friendly lookup feature to a sheet. My data consists of a list of names in column D with a list of sports and honors in columns O through CZ. For example:

D11: Joe Smith
O10:T10 (merged): Basketball
O11: Years Played
P11: Years Managed
Q11: Years Captained
R11: Years All Conference
S11: Years All County
T11: Years All Metro
U10:Z10 (merged): Badminton
U11: Years Played
etc.

What I'd like to do is utilize a combo box in H4 to pull up a name (I'd love to use autocomplete within this box but I know that's entirely another question) and have a formula yank the appropriate data and list only the sports played, how many years, etc. Something like this:

H4: Smith, Joe
I4: 4 Years Basketball
J4: 2 Years All Conference
I5: 2 Years Baseball
J5: 2 Years Manager
I6: 3 Years Lacrosse
J6: 2 Years All Conference
K6: 1 Year All Metro

Is there an easy way to do this? This sheet is already calculation-full, so much so that I've switched to manual calculations. Would it be easier to utilize a user-form? I'd need a lot of help creating that as well as I'm a complete rookie when it comes to VBA. Formulas I can work with; VBA... not so much.

Hope that all makes sense. Please excuse any stupid questions. Any help is very much appreciated!


Post your answer or comment

comments powered by Disqus
Let's say on Sheet1 I have one column:
1
A Apples
B Oranges
C Grapes
D Bananas

On Sheet2 I have a two columns:
1 2
A Grapes $2.00
B Ornges $3.00
C Bananas $4.00
D $5.00
E Grapes $2.00
F Appls $1.00

On Sheet3 I want to say:

Here is a list of items on SHEET2 which do NOT have a match on SHEET1: B1, D1, F1
(It lists those cells because they are either misspelled or empty.)

Is something like that possible?

Let me add that (after reading this terrific site and learning tons and tons from it) I THINK I could write a macro that would do this...but what I'm hoping for is some kind of dynamic spot on the spreadsheet...a formula...that would list all those cells that do NOT have a match.

Thank you.

Hello all,

I received quite a bit of help last week on a macro and I am to another stopping point for myself. I was able to write enough of the macro to get a decent start but I cannot seem to make the macro loop and list all of the cells that match the searched cell. Right now it only lists the first match, then moves on. I would rather have it list all of the matching cells and continue until the end of the list, then move to the next searchable term.

Here is my macro thus far.

Sub COMPARE()
    Dim wbk As Workbook
    strFirstFile = "c:Documents and SettingsmjbauerDesktopDocAttributes.xlsm"
    strSecondFile = "c:Documents and SettingsmjbauerDesktopMarietta_Facility_Export.xlsm"
    strThirdFile = "c:Documents and SettingsmjbauerDesktopCompare_TEST.xlsm"
    Set wbk = Workbooks.Open(strFirstFile)
    With wbk.Sheets("DocEntry")
        .Range("Z2:Z10000").Copy
    End With
    Set wbk = Workbooks.Open(strThirdFile)
    With wbk.Sheets("Sheet1")
        .Range("A2:A10000").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    End With
        
    ActiveWorkbook.Close SaveChanges:=True
    Set wbk = Workbooks.Open(strSecondFile)
    With wbk.Sheets("Export Worksheet")
        .Range("C2:C10000").Copy
    End With
    Set wbk = Workbooks.Open(strThirdFile)
    With wbk.Sheets("Sheet1")
        .Range("B2:B1000").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
    End With

    With wbk.Sheets("Sheet1")
        .Range("C2").FormulaR1C1 = "=IF(B2>0,IFERROR(ADDRESS(MATCH(B2,$A$1:$A$23,0),1,4,TRUE),"NOT
FOUND"),"")"
        .Range("C2").AutoFill Destination:=Range("C2:C10000"), Type:=xlFillDefault
    End With


End Sub

So as some background on the macro. It is taking two lists in two separate excel workbooks and combining the columns that should contain similar information. After it puts the matchable columns side by side it begins to match the entry in B1 to the entire A column. I have it to the point that it will list the first match it finds (lists it in Column C in the same row) but it will not list multiples.

Any thoughts on how to get it to list all of the matches as opposed to just one? Any help would be greatly appreciated.

Thank you all in advance, and if you would like me to build up dummy files to show exactly what I am looking for I would be glad to, I just didn't want to give a ton of information on here if it might just be a simple code change. Not sure as I am not a programming expert by any means. I actually know very little lol.

Mike

This is my first post so I hope I am following all the rules...

I am looking for some VBA code that will allow me to lookup a random cell in a dynamic range (the range will have 10 columns but thousands of rows). The random cell can be any cell in the range, but when it picks the cell, I want it to list the addresses of all the occurences of this value.

All the cells in the range will be names, so an example might be the macro picks "Tom Jones" and then lists B3, C78, D567 as the locations of "Tom Jones."

Let me know if I need to provide additional information.

Hi,

I need to find a formula that'll locate all the instances that match a string (e.g. contents of cell L18 = "Large") in a column/array (e.g. E2:E62 = all the cells contain the string "Small", "Medium" or "Large") and then use the location of those matching strings/cells to copy the contents of a parallel column/array (e.g D2:D62) to another area on the sheet (e.g. starting at L19).

I know I can use LOOKUP or INDEX (& 'Fill Down') to test each E cell & copy to (or leave blank) each corresponding J cell (from L19 to L80). But I'd really prefer to avoid this method because it means that I'm going to get a whole bunch of blank cells in my 'summary' column (L), will have to collapse that down by hand (using ctrl+G etc), and so won't be able to rely on the formulas to automatically update the summary list if the contents of Column E or Column D ever changed later on.

So, to simplify my question (leaving out the issue of matching to Column D), is there some way to copy ONLY the instances of E2:E62 that match the contents of L18, without having to 'Fill Down' & leave a ton of blank cells?? Is there a formula that will keep track of where the cell above it found a match (somehow) and will then continue searching from there, only stopping its search & displaying a result when it finds another match??

Actually, I wonder if it might not be easier to write a VBA function that could be called from each cell in L and would loop through the contents of Column E until it found a match....

Can anyone suggest a solution to my dilemma using built-in functions or a new VBA function??? I need help

Thanks for your help!

Hello All -

I am trying to create a macro that will prompt the user to select a folder to open, and then list all the file names that appear in that folder and all sub-folders within. The code below pretty much works (for some reason it does not pull files that only have numeric characters) but there is one more step i would like it to do. I am trying to not only get the macro to pull the name of the file, but also the name of the folder directly above it in another column. If anyone can advise how to do so I would be most appreciative. Thank you all for your time.

-d

ps. i'm using 2003 edition

Dim myFolder As String
Sub SelectFolder(Optional i_RootFolder As String)
Dim myShell As Object

Set myShell = CreateObject("Shell.Application")
If i_RootFolder = "" Then
'no root folder given, use default (which is Desktop)
Set myFolder = myShell.BrowseForFolder(0, "Please select a folder:", 1)
ElseIf Not (i_RootFolder Like "*[!0123456789]*") Then
'number for special folder given
Set myFolder = myShell.BrowseForFolder(0, _
"Please select a folder:", 1, CInt(i_RootFolder))
Else
'path for root folder given
Set myFolder = myShell.BrowseForFolder(0, _
"Please select a folder:", 1, CStr(i_RootFolder))
End If
If Not myFolder Is Nothing Then
SelectFolder = myFolder.Self.path
End If
End Sub

Sub TestListFilesInFolder()
'Dim folder As String

' folder = (myFolder)

Sheets.Add
With ActiveSheet
.Move after:=Worksheets(1)
.Name = "All Part #'s"
End With
Range("A1").Value = "Part #"
Range("B1").Value = "All Subparts"
Range("A1:B1").Font.Bold = True

ListFilesInFolder myFolder, True

' list all files included subfolders
End Sub
Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "C:FolderName", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.folder, SubFolder As Scripting.folder
Dim FileItem As Scripting.File
Dim r As Long
Set FSO = New Scripting.FileSystemObject
SourceFolder = FSO.GetFolder(SourceFolderName)
r = Range("A65536").End(xlUp).Row + 1
For Each FileItem In SourceFolder.Files
' display file properties
Cells(r, 1).Formula = FileItem.Name
' FileItem.Delete True
r = r + 1 ' next row number
Next FileItem
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFilesInFolder SubFolder.path, True
Next SubFolder
End If
Columns("A:H").AutoFit
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
ActiveWorkbook.Saved = True
End Sub

Hi all,

I have a large spreadsheet that is full of complicated formulas and named ranges.

Is is possible to scan the workbook and produce a list of all cells that have a formula or named range?

eg. the list would show

cell formula named range
A1 =MyRange+B2 MyRange

or something similar? Has anyone designed an Add-in/Utility that would do this?

Many thanks in advance

Hello, I need some expert advise, I create a spreadsheet every day with all adjustments made, all on 1 file with 100's of tabs (1 for each day).

I know if i highlight all tabs and then (Cntl f) i can search accross all tabs for a discription like "mars" if i keep pressing enter it will show me every thing with mars in the description. it sould be marsbars or mars planets etc it will still show me. but,

I want a vlookup and list formula that will search accross all tabs and list all what i type in, like if i type marsbar in the lookup, it will list every adjustment made accross all tabs with the "mars" some where in the description, it could be marsbar for eg.

I dont just want it to list the cells, i basically want the formula to lookup the description column accross all tabs, and then list the entire rows, Does any one know a formula please, my head is battered trying lol

my spreadsheets are like this

PRODUCT CODE DESCRIPTION DATE ADJUSTMENT COMMENTS

045404565 mars planet 12.01.04 -12 Damaged

I want it to list basically all adjustments on mars planets easily to see how
much the adjustment is over a month

I would like to be able to lookup the value of the first populated cell
to the right and 1 row below that of the active cell and the first
populated cell to the left and 1 row below that of the active cell.
The active is 1 row right above the range of cells with potentially
populated values, which is only 10 cells in lenth.

Is there a function or combination of functions that can accomplish
this? I would like to do this without using additional rows; however,
if absolutely necessary I'm willing to use one extra row.

I've slugged together a solution to find the value to the left, but in
doing this have used my one extra row and cannot figure out how to
accomplish the lookup to the right, without using another row.

For example, the formula in the 'active cell' would be able to find
and
use the 10 and 20 in the following cells:

row 1: blank blank 'active cell'
row 2: 10 blank blank blank 20 blank 15 blank blank 10

Thanks for help.

--
name
------------------------------------------------------------------------
name's Profile: http://www.excelforum.com/member.php...o&userid=34115
View this thread: http://www.excelforum.com/showthread...hreadid=562402

Hi Everyone, hope someone can help, looked everywhere but cannot find a solution. Here it goes;

I need a formula that can search through multiple tabs and list the matches.

I.E.
My excel document has 12 tabs, labelled Box 1, Box 2 etc. In each of those tabs, there are columns position (1 to 100) and name. Say i have Harry entered in Box 1 Position 29 and Box 10 Position 51, I need a formula that searches all tabs and lists all Harry's.

Is there a formula like that out there? I dont want to click on each tab indivually and use the CTRL+F function, because that will be too slow, and painful. i havent got a mocked up 'formula' added as i dont even know where to begin. but essentially i want to say in the first tab (PV BOX 1) put a formula anywhere in a free cell, and get the results listed.

Thank you so very much in advance.

**i have attached the data. i just made it up right now, easy to spot with just two tabs, and 10 samples a piece, but image 12 tabs with 100 samples. its a pain. i havent added a formula as i do not know where to bgin.

Hi,

I don't know if I can use VLOOKUP to do this.

There are 2 tables in 2 worksheets, I need to compare item codes in both worksheets and list out all descriptions in another column that matched.

However, in sheet 1 has same item codes with different description, if I use vlookup it only returns the first one that matched, not all.

Thanks.

Hi All,

I am after some code to create a new worksheet called 'xxxx' in my workbook, and then list all the sheets within the workbook (excluding the newly created 'xxxx' sheet.)

Hope someone can help.

Cheers

I would like to be able to lookup the value of the first populated cell
to the right and 1 row below that of the active cell and the first
populated cell to the left and 1 row below that of the active cell.
The active is 1 row right above the range of cells with potentially
populated values, which is only 10 cells in lenth.

Is there a function or combination of functions that can accomplish
this? I would like to do this without using additional rows; however,
if absolutely necessary I'm willing to use one extra row.

I've slugged together a solution to find the value to the left, but in
doing this have used my one extra row and cannot figure out how to
accomplish the lookup to the right, without using another row.

For example, the formula in the 'active cell' would be able to find and
use the 10 and 20 in the following cells:

row 1: blank blank 'active cell'
row 2: 10 blank blank blank 20 blank 15 blank blank 10

Thanks for help.

I would like to be able to lookup the value of the first populated cell
to the right and 1 row below that of the active cell and the first
populated cell to the left and 1 row below that of the active cell.
The active is 1 row right above the range of cells with potentially
populated values, which is only 10 cells in lenth.

Is there a function or combination of functions that can accomplish
this? I would like to do this without using additional rows; however,
if absolutely necessary I'm willing to use one extra row.

I've slugged together a solution to find the value to the left, but in
doing this have used my one extra row and cannot figure out how to
accomplish the lookup to the right, without using another row.

For example, the formula in the 'active cell' would be able to find and
use the 10 and 20 in the following cells:

blank blank 'active cell'
10 blank blank blank 20 blank 15 blank blank 10

Thanks for help.

Hello
Can anyone help me with a macro that will go to a particular folder in
Windows explorer and list all the files in one column of an excel
spreadsheet and the path to the file from that folder in another
column. I need this to work on sub-directories so effectively creating
an inventory of files and their paths.

Any ideas or help will be much appreciated!

All the best
Redge

Hi all,

I'm completely new to VBS and need to complete a project by 9am (BST) this morning!
I've tried recording macros etc but just cant get what Im trying to do to work.

Basically I have a sheet on a workbook named 'transfer' It is auto populated from various sources. I trying to create script I can allocate to a button that will select all populated cells on the 'transfer' sheet and save them to a txt file need Transfer.txt

Any help will be greatly appreciated.

hi guys

I found this code online ...

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox "A1 has changed."
End If
End Sub
It prompts the user if the value in A1 is changed.

Can this code be edited so it prompts the user for all populated cells.

I want this message to only appear if cells which already contain values are changed and not is the cell is blank.

many thanks

Im combining several sheet into one. During the selection process I end up starting at A1 and selecting all used cells however, I end up selecting some blank rows below the populated rows. It happens whether Im using
ActiveSheet.UsedRange.Rows.Select
or
Selection.CurrentRegion.Select

Im guess it picking up some old formating or blanks or???
Question is, how can I chose only the realy populated rows.
The rows are contiuously populated, no blanks in the field of data
thanks
Robert

I need to perform a different calculation based on the text "AB" being contained in a cell.
Simplified example: If "Cat" is in A1 then the formula in D1 would be =B1*C1
If A1 contains "AB" nested in a word the formula used would be =(B1/C1)*3.

I've seen how to do this if the AB was always in the same position but in this case the text string could be "Crab" or "Absolute" or "Alabama".

Is this even possible without using lookup and listing all possible words?

Thanks

I have a command button on sheet2 that i want to check Cells A1 to A200 to see if there are any duplicates. If there are any I would like a msgbox to pop up and list all the cells and values of the cells in the msgbox. I only want one msgbox to pop up.

Something like this

The following SN's have been entered more than once:

Abc123 can be located in Cells A2 & A10.
Def456 can be located in Cells A5 & A22.

I have data in 2 workbooks. Book1 - Sheet1 - Col A and Book2 - Sheet2 - Col A.
I want to compare Book2 with Book1 and list all values in Book2 that do not find a match in Book1

I want to run a macro for this please

I am trying to build a search field functionality to a workbook where the user would enter a search string and Excel will return the corresponding entries that contain the string.

I have been using the following array formula:
INDEX(List,SMALL(IF(ISERROR(SEARCH(Search_strings,TRANSPOSE(List))),"",TRANSPOSE(ROW(List)-MIN(ROW(List))+1)),ROW(List)-MIN(ROW(List))+1))

Where "Search_strings" is the range of the search string and "List" is the cells being evaluated for matches.

I can get the formula to return the first entry, but when I copy it down, it starts bringing up the next row whether it meets the criteria or not.

Any ideas????

Thanks!!

I have colour coded a spreadsheet and would like to set a cell to recognise
the couloured cells and add all these cells to a total

Hello all -

Here is what I am wanting to do: I have a spreadsheet that has X number
of cells populated in column A. I want to write a macro that will do the
following: concatenate all populated cells in column A into one cell,
separated by a comma (with no space).

Column A
A1
A2
A3

Becomes
A1,A2,A3

The cells in column A may vary depending on a given spreadsheet. I want
to be able to use the macro on any spreadsheet without having to tweak
the cell range manually.

Thanks!

Tom

--
TJM
------------------------------------------------------------------------
TJM's Profile: http://www.excelforum.com/member.php...o&userid=12746
View this thread: http://www.excelforum.com/showthread...hreadid=378139

Hi,
Is there a way via VBA to list all the cells that is to retrieve info from
other workbooks (i.e. cells with formulas such as
='D:[sample.xls]Sheet1'!A11)
TIA
J_J


No luck finding an answer? You could always try Google.