?
Solved

How to index Excel data

Posted on 2010-01-09
25
Medium Priority
?
299 Views
Last Modified: 2013-11-25
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
Comment
Question by:MaKK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 11
25 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26276039
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26276043
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
 

Author Comment

by:MaKK
ID: 26276194
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26276242
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
 

Author Comment

by:MaKK
ID: 26276286
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26276292
Yes, and then run the macro.

Kevin
0
 

Author Comment

by:MaKK
ID: 26276303
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26276313
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
 

Author Comment

by:MaKK
ID: 26276367
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26276387
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
 

Author Comment

by:MaKK
ID: 26276401
$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
 

Author Comment

by:MaKK
ID: 26276432
Er. What the.... something missing?

Max :)
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26276451
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
 

Author Comment

by:MaKK
ID: 26276534
Sorry, I'll get back to you asap tomorrow,  must go.

Cheers for now, thanks,

Max :)
0
 

Author Comment

by:MaKK
ID: 26280444
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26281116
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
 

Author Comment

by:MaKK
ID: 26288757
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
 

Author Comment

by:MaKK
ID: 26320335
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
 

Author Comment

by:MaKK
ID: 26365513
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
 
LVL 81

Expert Comment

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

Kevin
0
 

Author Comment

by:MaKK
ID: 26366096
Thanks Kevin,

Here's a sample sheet.....

Max  :)


MKSampleSheet.xlsx
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 26367266
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
 

Author Comment

by:MaKK
ID: 26367586
  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
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 1000 total points
ID: 26367766
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
 

Author Comment

by:MaKK
ID: 26368240
Thanks muchly Kevin....

Cheers for now,

Max  :)
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

741 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question