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?
JulyKnot
Posts: 189 Forumite
in Techie Stuff
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.
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.
0
Comments
-
Nope. I cannot see how it could be done.0
-
Pivot table, with col A filtered on "Easter" and showing just columns A-D and I-L???0
-
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 Apple0 -
Using FIND and =COLUMN() in a macro.0
-
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
EndNever let it get you down... unless it really is as bad as it seems.0 -
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!0 -
I'm no expert by a long way, but I'm given to understand that Access is more suited to non-numerical data?0
-
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.0 -
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.0
This discussion has been closed.
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