Avoid Convert to Number

Bob Wiebe

I frequently export Raiser's Edge Exports to Excel.  From there I often do a Vlookup on the constituent ID.  I am required to convert the Constituent ID column to numbers in order to complete this VLookup. Althuogh this is not too many keystrokes, I would prefer to avoid this step altogether. Perhaps there is something I am overlooking in the way I export which would make this converstion process unnecessary. I would elcome ideas.

Amy Dana

Bob Wiebe:

I frequently export Raiser's Edge Exports to Excel.  From there I often do a Vlookup on the constituent ID.  I am required to convert the Constituent ID column to numbers in order to complete this VLookup. Althuogh this is not too many keystrokes, I would prefer to avoid this step altogether. Perhaps there is something I am overlooking in the way I export which would make this converstion process unnecessary. I would elcome ideas.

How are you converting them now? And what exactly are you looking for with the VLookup? I'm wondering if there is a way you can query on just those records instead of manipulating an Excel spreadsheet.

Alan French

Rather than bothering to change the format of the constituent IDs, I usually alter my Vlookup slightly to search for the ID in a different format. For example, if the ID in A1 was in number format and the list being searched is formatted as text, then instead of looking up cell A1 I would use A1&"" (A1 concatenated with an empty string) , or VALUE(A1) if the ID is in text format and the other list is formatted as numbers.


If you've only got one or two columns of Vlookups then this might be quicker than how you're currently doing it. Presumably for very large spreadsheets it adds on a bit of calculating time, but usually I'm just cross-referencing small lists so I'm not too fussed about that.

James Andrews

Bob Wiebe:

I frequently export Raiser's Edge Exports to Excel.  From there I often do a Vlookup on the constituent ID.  I am required to convert the Constituent ID column to numbers in order to complete this VLookup. Althuogh this is not too many keystrokes, I would prefer to avoid this step altogether. Perhaps there is something I am overlooking in the way I export which would make this converstion process unnecessary. I would elcome ideas.

Yes, RE exports to Excel with that leading apostrophe that forces Excel to look at every cell as plain text. To avoid this problem, what I do (and what I recommend to all our users here) is export to character separated values, then open the resulting .CHR file in Excel.  Excel is remarkably good at interpreting data types from plain text files, and it almost always figures out what's a number, text, date, currency, etc.

Bob Wiebe

Alan French:

Rather than bothering to change the format of the constituent IDs, I usually alter my Vlookup slightly to search for the ID in a different format. For example, if the ID in A1 was in number format and the list being searched is formatted as text, then instead of looking up cell A1 I would use A1&"" (A1 concatenated with an empty string) , or VALUE(A1) if the ID is in text format and the other list is formatted as numbers.


If you've only got one or two columns of Vlookups then this might be quicker than how you're currently doing it. Presumably for very large spreadsheets it adds on a bit of calculating time, but usually I'm just cross-referencing small lists so I'm not too fussed about that.


Thanks, Alan. This looks promising.

 

Zane Magnuson 2

Bob Wiebe:

I frequently export Raiser's Edge Exports to Excel.  From there I often do a Vlookup on the constituent ID.  I am required to convert the Constituent ID column to numbers in order to complete this VLookup. Althuogh this is not too many keystrokes, I would prefer to avoid this step altogether. Perhaps there is something I am overlooking in the way I export which would make this converstion process unnecessary. I would elcome ideas.

Instead of using VLOOKUP, you could always use INDEX/MATCH. It's much more dynamic and powerful than VLOOKUP formulas, it can handle the IDs without converting them, and it doesn't require the IDs to be sorted in order. The way it works is as follows:


INDEX asks for an array (i.e. your entire spreadsheet), then a row and column number within that array:


=INDEX([array range], [row], [column])


So if you do "=INDEX(A:E,1,3)", you'll get whatever is in cell C1 (row 1, column 3).


Then all you do is pair that with MATCH. MATCH wants a lookup value (i.e. the Constituent ID you want to lookup), an array (i.e. the column with all the Constituent IDs), and a match type:


=MATCH([lookup value],[array],[match type])


For [Match Type], you'll pretty much always want to use "0" for an exact match. If all your Constituent IDs are in Column A and you're trying to lookup the constituent in cell A3, for example, it would look like this:


=MATCH(A3,A:A,0)


So now you can pair that with INDEX to basically say "Here's my entire spreadsheet. I want to get data from a particular cell based on the Constituent ID. Match the Constituent ID in my lookup cell to find the row number, then give me that data from X column in the same row". Like so:


=INDEX([range of spreadsheet],MATCH([Constituent ID lookup],[Column of Constituent IDs],0),[Column with data you want for the constituent])


Like I said, this will provide better results and is more dynamic than VLOOKUP. The drawback is that it's a slightly more complicated formula to type (you get used to it once you see how good it is), and this will treat converted numbers and unconverted numbers as different things. So if you are trying to compare sheets with unconverted IDs straight from RE and converted IDs that were typed in by hand, it won't recognize that they are equal. However, if you are doing all your exports straight from RE and don't convert the IDs to numbers, it'll work beautifully.