Categories: Sheets : Ask a "how-to" question :

Spreadsheat look and compare

Showing 1-11 of 11 messages
Spreadsheat look and compare JanHL 5/6/12 3:14 AM
Hi,

Some time ago i was fortunate to have a friend help me with a excel spreadsheet which checks my creditcard statements and the indexes them
by category like, food, car etc.
I am trying to do the same in google docs, but my formula doesn't work and i haven't got a clue how to make array search functions in google docs.

I am trying to find a store name and then return the amount into a cell.

I have shared a document here - https://docs.google.com/spreadsheet/ccc?key=0AsYi4MxPFJ_6dDBPSEJOUmZ2TzZVNzZpZHVPLVFJUkE

I hope that someone can help me with this? Any tips or tricks will be greatly appreciated.

Kind regards...Jan
Re: Spreadsheat look and compare MarinusP 5/6/12 5:45 AM
Hi

I've added a copy-sheet to your spreadsheet, put a formula in cell F2, and dragged it to the right. I hope this is as you wish.

Please let me know if you have any questions or suggestions.
Re: Spreadsheat look and compare JanHL 5/6/12 5:47 AM
Hi,


Thank you  very much, this was exactly was i was after!


Thanks.



Re: Spreadsheat look and compare MarinusP 5/6/12 6:10 AM
Hi

I just noticed that mu formula probably would not give the correct results, with the words:
  • Legekæden
  • UNO-X
  • X-tra
because of the æ, and the - . Also it seems that you have your spreadsheet no longer shared.

So please replace the formula with this one:

=ARRAYFORMULA(IF($C2:$C9<>"";IF(IFERROR(MATCH(REGEXREPLACE(SUBSTITUTE(SUBSTITUTE($C2:$C9;"Dankort";);"Nota";);"[ 0-9]";);StoreNames!B:B;));$D2:$D9;);))

And check if it gives the correct results in all situations.
Re: Spreadsheat look and compare JanHL 5/6/12 8:11 AM
Hi again,

Thank you for your follow!

I forgot to mention that there will not always be a "dankort", "nota" etc, i is different from statement to statement.
Is there a way to change your formula to only search for the store name?
Re: Spreadsheat look and compare MarinusP 5/6/12 9:43 AM
Hi

If you mean, in column C would be only the store name, it will make things much easier. Then the formula can simply be:

=ARRAYFORMULA(IF($C2:$C9<>"";IF(IFERROR(MATCH($C2:$C9;StoreNames!B:B;));$D2:$D9;);))

In that case the names should be identical, for example, no "McDonalds" and "Mc Donalds".

Or do you mean in column C would be: random-text store-name random-text ?
Re: Spreadsheat look and compare JanHL 5/6/12 10:24 AM
I mean that sometimes it says

Dankort itunes nota20334

Other times it says

Handlet itunes ref. ordre 38574587

or similar, so it will only be the actual store name that can be used for the search
and formatting of the string. All the other information will change now and again....
Re: Spreadsheat look and compare JanHL 5/6/12 10:26 AM
Btw. the spreadsheet is online again!
Re: Spreadsheat look and compare MarinusP 5/6/12 12:05 PM
Hi

Yes, I noticed that you shared the spreadsheet again.

So, column C should contain the name of the store, but it might also contain random text like: Handlet ref. ordre Dankfort Nota ...... 

Well, I've added an extra column, with formulas dragged down, to make it more clear. I'm not sure if it's possible with a formula which not needed to be dragged down. perhaps Adam or Yogi  will have a look at it.

Also I added a new copy, because the name "Legekæden" was not in the original.
Re: Spreadsheat look and compare JanHL 5/10/12 6:58 AM
Hi again,

Having tested it out, it works fine although there is a slight problem when 2 store names contains some of the same names like,
Ilva and Silvan. Then it marks it a "IlvaSilvan" and other highly creative names ;-)


Re: Spreadsheat look and compare MarinusP 5/10/12 12:41 PM
Hi

Yes, that's logical because my formula didn't expect more then one match; since there are two names that can be found in "Silvan", both are returned by the formula.

I have now revised my formula, so that, if there is more then one match, only the longest name is returned. So, if a string contains both "Ilva" and "Silvan", then only the longest: "Silvan" is returned.

It has become a pretty tedious formula now, though......

A better way would be to search for <space><name><space> e.g. " Silvan " or " Ilva ", but I had to remove spaces, otherwise "Mc Donalds" (with space) will not match "McDonalds" (without space).