Microsoft Office Forums Look up question

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 01-25-2019, 11:09 AM
rossmoyne rossmoyne is offline Look up question Windows 7 32bit Look up question Office 2007
Novice
Look up question
 
Join Date: Feb 2013
Posts: 9
rossmoyne is on a distinguished road
Default Look up question

I downloaded data from my bank in a csv file, saved it as a spreadsheet. I need to create a search / look up function so I can go through all that bank data and calculate my expenses at different places for different time frames.



The problem is that the data looks like this:

“WAL-MART #1234 01-01-01 NEW YORK NY 9876 DEBIT CARD PURCHASE-PIN”

Of course, I have multiple purchases at Walmart, and the description is a little different for each one.

How do I do a lookup function to capture all the Wal-mart purchases throughout a given timeframe?

Or is there a better way than the look up function?


Thanks.
Reply With Quote
  #2  
Old 01-26-2019, 12:39 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Look up question Windows 7 64bit Look up question Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,396
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Please post a sample sheet with some data and expected results ( no pics please). Thanks
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #3  
Old 02-04-2019, 07:23 PM
rossmoyne rossmoyne is offline Look up question Windows 7 32bit Look up question Office 2007
Novice
Look up question
 
Join Date: Feb 2013
Posts: 9
rossmoyne is on a distinguished road
Default

I know it's been a while, how to I attach a spreadsheet?
Reply With Quote
  #4  
Old 02-04-2019, 11:22 PM
ArviLaanemets ArviLaanemets is offline Look up question Windows 8 Look up question Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

This data row looks very strange for csv-file! So long I have encountered only comma and semicolon as csv-file delimiter.

Or is it text-file instead? And what is the delimiter - space or tab? When space, then you have a problem, as I see at least 3 parts of string which contain several words and contain additional spaces because this.

Anyway you need to split this string into separate columns in Excel. For this you have 2 options.

1. Simply open the file with Excel. When all entries remain in single column, use Text-To-Columns feature to split the column. You are asked for delimiter - set it tab or space depending on your data. NB! When delimiter is space, then e.g. "NEW YORK NY" is split into 3 different columns - this means that from different rows the sum is stored in different columns, so you have to edit the table manually after splitting;
2. You rename the file as *.txt, Open a new instance of Excel, and open this text file using Open Dialog. You are asked for column delimiter - choose right one. You also can determine data formats for every identified column. Proceed. All columns are split when the file is opened. NB!. When the delimiter was space, you again have to correct the result.
Reply With Quote
  #5  
Old 02-05-2019, 12:29 AM
Pecoflyer's Avatar
Pecoflyer Pecoflyer is offline Look up question Windows 7 64bit Look up question Office 2010 64bit
Moderator
 
Join Date: Nov 2011
Location: Brussels Belgium
Posts: 2,396
Pecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of lightPecoflyer is a glorious beacon of light
Default

Quote:
Originally Posted by rossmoyne View Post
I know it's been a while, how to I attach a spreadsheet?
Click " Go advanced - Manage attachments" and follow the wizard
__________________
Problem solved ? Let others know by clicking " Thread Tools" then " Mark thread as solved".( This can be undone if need be)
Want to thank for the help received ? Click the scales symbol in the upper right corner of a post from the person you want to thank.
Reply With Quote
  #6  
Old 02-05-2019, 06:38 AM
rossmoyne rossmoyne is offline Look up question Windows 7 32bit Look up question Office 2007
Novice
Look up question
 
Join Date: Feb 2013
Posts: 9
rossmoyne is on a distinguished road
Default Search function question

Attached is a short spreadsheet with the download information and the expected results.
Attached Files
File Type: xlsx Search Function Formula question.xlsx (10.1 KB, 2 views)
Reply With Quote
  #7  
Old 02-05-2019, 07:34 AM
ArviLaanemets ArviLaanemets is offline Look up question Windows 8 Look up question Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

In case the list of companies is not too long, the solution in attached workbook may be an option.

The named ranges nName## are less prone to errors, when search strings are not very short (this is the cause I limited the search for of string "WM" to 5 leftmost characters of description). The worst error is, when several names nName## are returning non-zero responses - those will be summarized, and as the result a wrong company may be returned.

Theoretically you can search for 254 different companies typed-in directly into formula. To have more companies, you must have a sheet with list of companies, and the CHOOSE() function must refer to this list instead typed-in one.

You can use autofilter to filter any set of entries from data table.

When you add a couple of empty rows at top of table (in case the data table is defined as a Table, it is good idea to keep an empty row between entries not in Table and Table), then into field C1 you can enter the formula like
Code:
=SUBTOTAL(9,tData[Amount])
Now whenever you set any autofilter for your data table, the total amount for filtered rows is returned by the formula.
Attached Files
File Type: xlsx StringSearch.xlsx (12.7 KB, 11 views)
Reply With Quote
  #8  
Old 02-05-2019, 06:16 PM
rossmoyne rossmoyne is offline Look up question Windows 7 32bit Look up question Office 2007
Novice
Look up question
 
Join Date: Feb 2013
Posts: 9
rossmoyne is on a distinguished road
Default

Arvilaaements, I understand what you put in your spreadsheet, seems pretty simple, but I have no idea what you're talking about in the rest of your post.

Thanks in advance for any explanation.
Reply With Quote
  #9  
Old 02-06-2019, 12:56 AM
ArviLaanemets ArviLaanemets is offline Look up question Windows 8 Look up question Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Quote:
...I understand what you put in your spreadsheet, seems pretty simple, but I have no idea what you're talking about in the rest of your post.
About totals for filtered rows? See attached!
Attached Files
File Type: xlsx StringSearch.xlsx (12.9 KB, 3 views)
Reply With Quote
  #10  
Old 02-13-2019, 08:18 PM
rossmoyne rossmoyne is offline Look up question Windows 7 32bit Look up question Office 2007
Novice
Look up question
 
Join Date: Feb 2013
Posts: 9
rossmoyne is on a distinguished road
Default

Finally getting around to try and apply this to my spreadsheet, and having zero luck.

I cut and pasted the string search into a column and all it did was copy the original example that was provided.

Is there any special formating required for the spreadsheet?
Reply With Quote
  #11  
Old 02-13-2019, 08:32 PM
rossmoyne rossmoyne is offline Look up question Windows 7 32bit Look up question Office 2007
Novice
Look up question
 
Join Date: Feb 2013
Posts: 9
rossmoyne is on a distinguished road
Default

and how does this formula pass over a row that doesn't have any of the names I'm looking for?
Reply With Quote
  #12  
Old 02-14-2019, 01:47 AM
ArviLaanemets ArviLaanemets is offline Look up question Windows 8 Look up question Office 2016
Expert
 
Join Date: May 2017
Posts: 463
ArviLaanemets will become famous soon enoughArviLaanemets will become famous soon enough
Default

Have you entered according Names into your workbook? To see defined names, select from menu Formulas > Name Manager. In attached capture you see Names nName01 ... nName07 - one for every string what is searched, and a namr nNameNum, where values of those 7 Names are summarized.

When you need to search for more strings, you have to add a name like nName## for every new string, and add new Names values into nNameNum. after that the formula in worksheet will work.

When Description doesn't have any of strings used in defined Names in it, the worksheet formula returns an error code #VALUE. You can wrap the formula with IFERROR() function, like:
Code:
=IFERROR(CHOOSE(nNameNum,"Eye Care","Mapco","Orthopaedic",...),"No search string exixst in this description")
Attached Images
File Type: jpg Capture.JPG (122.5 KB, 5 views)
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Look up question Macro Needed to Insert Asnwer to A Question in Multiple Choice Format Question rsrasc Word VBA 7 03-28-2014 12:28 PM
Question ... Need Help MdCadle PowerPoint 1 09-10-2012 09:04 AM
ppt question fat PowerPoint 0 10-06-2011 01:33 PM
Look up question One to many and many to one question Girlie4 Excel 3 08-09-2009 08:02 PM
Categories question & replying with attachment question glitzymama Outlook 0 03-15-2006 09:32 AM


All times are GMT -7. The time now is 08:20 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2019, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2019 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft