• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

How to index Excel data

Hi!

Is it possible to create an index of cell data (in my case personal names)? And if so can it be done so that each entry is a link to click to go to the cell?

Would save a lot of manual labour....

With thanks,

Max

P.S. Am running Excel 2010
0
MaKK
Asked:
MaKK
  • 14
  • 11
1 Solution
 
zorvek (Kevin Jones)ConsultantCommented:
This is your lucky day, Max.

Add this macro to any general code module:

Public Sub CreateIndex()

    Dim Source As Range
    Dim Target As Range
    Dim SourceCell As Range
    Dim AddIndex As Boolean
   
    If Selection.Areas.Count <> 2 Then
        MsgBox "With the CTRL key held down, select the source data and then click in the cell where you want the index."
        Exit Sub
    End If
   
    Set Source = Selection.Areas(1)
    Set Target = Selection.Areas(2)
   
    Source.Sort Key1:=Source
    For Each SourceCell In Source
        AddIndex = False
        If SourceCell.Row = Source.Row Then
            AddIndex = True
        ElseIf UCase(Left(SourceCell, 1)) <> UCase(Left(SourceCell.Offset(-1), 1)) Then
            AddIndex = True
        End If
        If AddIndex Then
            Sheet1.Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:="'" & Selection.Parent.Name & "'!" & SourceCell.Address, TextToDisplay:=UCase(Left(SourceCell, 1))
            Set Target = Target.Offset(1)
        End If
    Next SourceCell

End Sub

To create the index, hold down the CTRL key, select the source data and then click in the cell where you want the index, then run the macro.

Kevin
0
 
zorvek (Kevin Jones)ConsultantCommented:
You will notice that when following hyperlinks, Excel does not position the target cell in a nice way. The instructions below provide a workaround.

When a hyperlink is followed to the target cell Excel scrolls the view just enough to bring the target cell into view. That means that of the view is above or to the left the target cell then the target cell will appear at the bottom or the right of the view. To always move the view such that the target cell is in the top left corner of the viewable area, add the code below to the ThisWorkbook VBA code module.

Note that this solution will not work if using the HYPERLINK function as that technique does not generate a "FollowHyperlink" event which is required for this solution.

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
    If Len(Target.Address) = 0 Then
        On Error Resume Next
        Application.Goto Reference:=Application.Evaluate(Target.SubAddress)
        ' Position in center of window
        ActiveWindow.ScrollRow = Application.Evaluate(Target.SubAddress).Row - Int(ActiveWindow.VisibleRange.Rows.Count / 2 - 1)
        ActiveWindow.ScrollColumn = Application.Evaluate(Target.SubAddress).Column - Int(ActiveWindow.VisibleRange.Columns.Count / 2 - 1)
        ' Position in top left corner
        ActiveWindow.ScrollRow = Application.Evaluate(Target.SubAddress).Row
        ActiveWindow.ScrollColumn = Application.Evaluate(Target.SubAddress).Column
        ' Position in row and one column down from the top left corner
        ActiveWindow.ScrollRow = Application.Evaluate(Target.SubAddress).Row - 1
        ActiveWindow.ScrollColumn = Application.Evaluate(Target.SubAddress).Column - 1
    End If
End Sub

Kevin
0
 
MaKKAuthor Commented:
Hey thanks Kevin!

Sorry mate it's not working for me.  
I hold down Ctrl, select say column B, click in one cell in column F, run the macro, let go of Ctrl.
Correct?

Also, I only want to index text strings (in cells) starting with a capital letter (e.g. Adam).

Max  :)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
zorvek (Kevin Jones)ConsultantCommented:
Don't select the entire column, just the cells containing the data you want to index. It will index all values in that selection.

Kevin
0
 
MaKKAuthor Commented:
Nothing yet...

This time -   I hold down Ctrl, select  a range of data, click in one cell in another column, run the macro, let go of Ctrl.  Correct?

Max :)
0
 
zorvek (Kevin Jones)ConsultantCommented:
Yes, and then run the macro.

Kevin
0
 
MaKKAuthor Commented:
Sorry....

Hold down Ctrl,
Select  a range of data,
Select one cell in another column,
Let go of Ctrl.
Run macro.

Macro displays message box with instructions, click OK,

Nothing.... :(

Max :)
0
 
zorvek (Kevin Jones)ConsultantCommented:
When you select the range in the first column you have to do so in one action. Each time you click the mouse you create another selection "region" and the macro is looking for only two regions. Are there values in the list that you do not want to index and are they not being selected?

Kevin
0
 
MaKKAuthor Commented:
No I selected a range of cells within a continuous list of (plain text) names.
Tried using click & drag, and also Ctrl.+ Shift + down arrow.

Back to you!


Max :)
0
 
zorvek (Kevin Jones)ConsultantCommented:
Make your selection and run this macro:

Public Sub FigureOutWhatMaxIsDoing()
   Dim Area As Range
   For Each Area In Selection
      Debug.Print Area.Address
   Next Area
End Sub

Press ALT+F11, press CTRL+G, press CTRL+A, press CTRL+C, and paste the clipboard here.

Kevin
0
 
MaKKAuthor Commented:
$F$4
$F$5
$F$6
$F$7
$F$8
$F$9
$F$10
$F$11
$F$12
$F$13
$F$14
$F$15
$F$16
$F$17
$F$18
$F$19
$F$20
$F$21
$F$4
$F$5
$F$6
$F$7
$F$8
$F$9
$F$10
$F$11
$F$12
$F$13
$F$14
$F$15
$F$16
$F$17
$F$18
$F$19
$F$20
$F$21
$F$4
$F$5
$F$6
$F$7
$F$8
$F$9
$F$10
$F$11
$F$12
$F$13
$F$14
$F$15
$F$16
$F$17
$F$18
$F$19
$F$20
$F$21
$G$23

How about that!
Max :)
0
 
MaKKAuthor Commented:
Er. What the.... something missing?

Max :)
0
 
zorvek (Kevin Jones)ConsultantCommented:
OK, you are clicking on each individual cell. You need to, while pressing the CTRL key down, click down on the first cell, drag to the last cell, and then release the mouse button.

Kevin
0
 
MaKKAuthor Commented:
Sorry, I'll get back to you asap tomorrow,  must go.

Cheers for now, thanks,

Max :)
0
 
MaKKAuthor Commented:
Made a selection again,

$C$38
$E$32
$E$33
$E$34
$E$35
$E$36
$E$37
$E$38
$E$39
$E$40
$E$41
$E$42
$E$43
$E$44
$E$45
$E$46
$E$47
$E$48
$E$49
$F$32

Ok?

Tried the CreateIndex macro again but still not working

Um,  here's an idea I had overnight that hopefully will clarify what my question envisioned, so to speak.  

MaKKro CreateDataIndex()

Create Index sheet
Go to first active cell
RetrieveData:
      If active cell contains text string beginning with uppercase letter (string may include hyphen and space)
      Retrieve string and cell number
      Convert to hyperlink
      Add to Index sheet
      End If
Goto next active cell

If text string exists in Index sheet
Go to next active cell

If text string not in Index sheet
Go to RetrieveData

Stop somehow ? at given range?
End MaKKro

Hope this  is helpful... ;)

Max

0
 
zorvek (Kevin Jones)ConsultantCommented:
This shouldn't be that difficult. I can select a range of cells using one single mouse motion and then the destination cell. When I run the macro I see:

$A$1:$A$20
$C$1

Keep trying to select the first set of cells as follows: mouse down on the top cell and, without releasing the mouse button, drag the mouse to the end of the list, release the mouse button, then click on the destination cell. Do all of this while holding down the CTRL key.

Kevin
0
 
MaKKAuthor Commented:
Ok, I followed your instructions repeatedly but still no Index!  Nothing appears in the VBA Immediate window.
I notice when I hold down Ctrl. The currently selected cell  highlights. The I click and drag the list of names to index.  This might explain $C$38 appearing at the top of list in my previous comment.?

Max :)
0
 
MaKKAuthor Commented:
Hi Kevin,

Here's success!(?)   See attached file:

Did it this way:
Point mouse to first cell in list of names.
Select & hold on first cell.
Hold down Ctrl.
Drag to end of list of names.
Point mouse to & select first cell for index
Release Ctrl.
Run macro.

Max :)




MakeIndexTest.xlsx
0
 
MaKKAuthor Commented:
Hi Kevin,

 Sorry, as the names I need to index aren't in a continuous list I can't see how this method will suffice for my purposes.  Please consider my macro idea in the previous comment 11/01/10 02:58 PM, ID: 26280444.

Thanks,

Max :)

0
 
zorvek (Kevin Jones)ConsultantCommented:
Can you upload a sample workbook that more accurately reflects your actual situation?

Kevin
0
 
MaKKAuthor Commented:
Thanks Kevin,

Here's a sample sheet.....

Max  :)


MKSampleSheet.xlsx
0
 
zorvek (Kevin Jones)ConsultantCommented:
Unfortunately what you are looking doesn't make any sense. I thought you had a sorted list of names and you wanted to create an index into the first name in each letter of the alphabet.

What you have is a list of unsorted names. The best I can do is create a list of links to each name but it would still be half as long as the main list (one name per row instead of one name every other row.)

The bottom line is: what's the point?

Kevin
0
 
MaKKAuthor Commented:
  Well I'd like to have a separate sheet listing of all the people mentioned in the table(s) with a hyperlink that would take you to the first instance only of their names. The point is to provide a an easy way to navigate the workbook. Does this help?

Max
0
 
zorvek (Kevin Jones)ConsultantCommented:
I'm sorry, but the goal seems a little silly given all the names appear in the first few rows. In any event, see the attached workbook. Perhaps your real data is different.

Download the workbook. Change the .txt extension to .zip. Unzip it. Change the .txt extension of the unzipped file to .zip. Unzip it to get the .xlsm workbook.

The macro is already installed.

Kevin
MKSampleSheet.txt
0
 
MaKKAuthor Commented:
Thanks muchly Kevin....

Cheers for now,

Max  :)
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 14
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now