Techie Stuff

Techie Stuff

We'd like to remind Forumites to please avoid political debate on the Forum. This is to keep it a safe and useful space for MoneySaving discussions. Threads that are - or become - political in nature may be removed in line with the Forum’s rules. Thank you for your understanding.

Do you think this can be achieved with Excel?

If I had a key word that I was interested in searching the entire spreadsheet,
I would want to see only the columns that contained the word plus the next three column next to it.

Eg if the word "Easter" appeared in columns A and I only, I would want to see Columns A to D and I to L only for that particular search.

Too complex? I was thinking of having a cell with just the key word in as the reference point that could be changed for every new search.

Welcome!

It looks like you're new here. Sign in or register to get started.
«13

Comments

  • Posts: 5,389 Forumite
    Part of the Furniture 1,000 Posts Name Dropper I've been Money Tipped!
    Nope. I cannot see how it could be done.
  • Posts: 1,165 Forumite
    Part of the Furniture 1,000 Posts Combo Breaker
    Pivot table, with col A filtered on "Easter" and showing just columns A-D and I-L???
  • Posts: 11,730 Forumite
    Part of the Furniture 10,000 Posts Name Dropper Photogenic
    Pivot table the best bet....
    ......Gettin' There, Wherever There is......

    I have a dodgy "i" key, so ignore spelling errors due to "i" issues, ...I blame Apple :D
  • Posts: 143 Forumite
    Using FIND and =COLUMN() in a macro.
  • Posts: 445 Forumite
    Part of the Furniture 100 Posts Name Dropper Photogenic
    How are you with macros? The following will seach for cells (in the active sheet) with a search string then hide every column except for columns with the search phrase and the three columns to the right of those columns. If you don't enter any search string then all columns are unhidded.

    Option Explicit

    Sub SearchAndHide()

    Dim strSearch As String

    strSearch = InputBox(Title:="Search and Hide", Prompt:="Enter Search String", Default:="")

    If Not (strSearch = "" Or strSearch = vbNullString) Then

    DoSearch strSearch

    Else

    DoSearch ""

    End If

    End Sub


    Sub DoSearch(ByVal strSearch As String)

    Dim rnge As Range
    Dim col As Range
    Dim aCell As Range
    Dim ffCellr As Long
    Dim ffCellc As Long
    Dim foundFirst As Boolean
    Dim foundLast As Boolean
    Dim Hide() As Boolean
    Dim c As Long

    Set aCell = ActiveSheet.Cells(1, 1)

    Set rnge = Range(aCell, Cells(ActiveSheet.UsedRange.Rows.Count, ActiveSheet.UsedRange.Columns.Count))

    ReDim Hide(rnge.Columns.Count + 3)

    For c = 1 To rnge.Columns.Count
    Hide(c) = True
    rnge.Cells(1, c).EntireColumn.Hidden = False
    Next

    If strSearch = "" Then
    Exit Sub
    End If

    ffCellr = -1
    ffCellc = -1

    Do Until (aCell Is Nothing) Or foundLast

    If foundFirst Then
    ffCellr = aCell.Row
    ffCellc = aCell.Column
    foundFirst = False
    End If

    Set aCell = rnge.Find(What:=strSearch, LookIn:=xlValues, _
    after:=aCell, _
    LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
    MatchCase:=False, SearchFormat:=False)

    If Not aCell Is Nothing Then

    If ffCellr = -1 Then
    foundFirst = True
    End If

    If ((ffCellr = aCell.Row) And (ffCellc = aCell.Column)) Then
    foundLast = True
    Else
    For c = 0 To 3
    Hide(aCell.Column + c) = False
    Next
    End If

    End If

    Loop

    For c = 1 To rnge.Columns.Count
    rnge.Cells(1, c).EntireColumn.Hidden = Hide(c)
    Next

    End
    Never let it get you down... unless it really is as bad as it seems.
  • Posts: 13,543 Forumite
    Excel is brilliant, very powerful, and thanks to vba can be forced to do just about anything. The downside is that the more off-piste you get, the further you get from what excel does best, so the more problems you create for yourself. Without a proper thorough understanding of your data, requirements, implementation and use cases you could never be fully confident the resultant monster application wasn't losing data or corrupting results.

    At the moment you have a problem - automate it blindly and then you'll have 2 problems!

    What exactly are you trying to achieve? There are no new problems in IT just the same old ones presented differently. Go back to the actual core problem and there may be far more appropriate tools to solve it!
  • Posts: 1,195 Forumite
    I'm no expert by a long way, but I'm given to understand that Access is more suited to non-numerical data?
  • Posts: 189 Forumite
    Thanks for your replies. I intend to use them to read up on the various functions that I know little about.

    I have very basic understanding of excel. The most complex formula I have ever created to date is nested formulas containing IF.

    Reason for my query.
    My start spreadsheet is just going to be a giant store containing all the data ( grouped into columns four wide- ie data in Columns B to D relate to Column A and so on)

    I want to the search by keyword and weed out the unwanted columns so that I can then copy results of the search to a CSV file and import into an app that allows import of csv files only.
  • Posts: 189 Forumite
    Stooby2 wrote: »
    I'm no expert by a long way, but I'm given to understand that Access is more suited to non-numerical data?
    Yes, it's the csv file restriction that made me think about excel.
  • Posts: 6,460 Forumite
    Part of the Furniture 1,000 Posts Photogenic Combo Breaker
    edited 1 April 2013 at 8:07PM
    Stooby2 wrote: »
    I'm no expert by a long way, but I'm given to understand that Access is more suited to non-numerical data?

    Access is more suited to randomly eating your data, numerical or otherwise.

    Unless this is for a one off, or purely for personal use, do not use Access, and if you must then store your data elsewhere.
This discussion has been closed.
Meet your Ambassadors

Welcome!

It looks like you're new here. Sign in or register to get started.

Categories

  • All Categories
  • 347.2K Banking & Borrowing
  • 251.6K Reduce Debt & Boost Income
  • 451.8K Spending & Discounts
  • 239.5K Work, Benefits & Business
  • 615.4K Mortgages, Homes & Bills
  • 175.1K Life & Family
  • 252.8K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 16K Discuss & Feedback
  • 15.1K Coronavirus Support Boards

Is this how you want to be seen?

We see you are using a default avatar. It takes only a few seconds to pick a picture.