#1
|
|||
|
|||
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. |
#2
|
||||
|
||||
Please post a sample sheet with some data and expected results ( no pics please). Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
I know it's been a while, how to I attach a spreadsheet?
|
#4
|
|||
|
|||
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. |
#5
|
||||
|
||||
Click " Go advanced - Manage attachments" and follow the wizard
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#6
|
|||
|
|||
Search function question
Attached is a short spreadsheet with the download information and the expected results.
|
#7
|
|||
|
|||
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]) |
#8
|
|||
|
|||
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. |
#9
|
|||
|
|||
Quote:
|
#10
|
|||
|
|||
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? |
#11
|
|||
|
|||
and how does this formula pass over a row that doesn't have any of the names I'm looking for?
|
#12
|
|||
|
|||
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") |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
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 |
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 |