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). |