Avoid Convert to Number
Comments
-
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.
0 -
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.3 -
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.
1 -
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.
0 -
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.
7
Categories
- All Categories
- Shannon parent
- shannon 2
- shannon 1
- 21 Advocacy DC Users Group
- 14 BBCRM PAG Discussions
- 89 High Education Program Advisory Group (HE PAG)
- 28 Luminate CRM DC Users Group
- 8 DC Luminate CRM Users Group
- Luminate PAG
- 5.9K Blackbaud Altru®
- 58 Blackbaud Award Management™ and Blackbaud Stewardship Management™
- 409 bbcon®
- 2.1K Blackbaud CRM™ and Blackbaud Internet Solutions™
- donorCentrics®
- 1.1K Blackbaud eTapestry®
- 2.8K Blackbaud Financial Edge NXT®
- 1.1K Blackbaud Grantmaking™
- 527 Education Management Solutions for Higher Education
- 1 JustGiving® from Blackbaud®
- 4.6K Education Management Solutions for K-12 Schools
- Blackbaud Luminate Online & Blackbaud TeamRaiser
- 16.4K Blackbaud Raiser's Edge NXT®
- 4.1K SKY Developer
- 547 ResearchPoint™
- 151 Blackbaud Tuition Management™
- 61 everydayhero
- 3 Campaign Ideas
- 58 General Discussion
- 115 Blackbaud ID
- 87 K-12 Blackbaud ID
- 6 Admin Console
- 949 Organizational Best Practices
- 353 The Tap (Just for Fun)
- 235 Blackbaud Community Feedback Forum
- 55 Admissions Event Management EAP
- 18 MobilePay Terminal + BBID Canada EAP
- 36 EAP for New Email Campaigns Experience in Blackbaud Luminate Online®
- 109 EAP for 360 Student Profile in Blackbaud Student Information System
- 41 EAP for Assessment Builder in Blackbaud Learning Management System™
- 9 Technical Preview for SKY API for Blackbaud CRM™ and Blackbaud Altru®
- 55 Community Advisory Group
- 46 Blackbaud Community Ideas
- 26 Blackbaud Community Challenges
- 7 Security Testing Forum
- 3 Blackbaud Staff Discussions
- 1 Blackbaud Partners Discussions
- 1 Blackbaud Giving Search™
- 35 EAP Student Assignment Details and Assignment Center
- 39 EAP Core - Roles and Tasks
- 59 Blackbaud Community All-Stars Discussions
- 20 Blackbaud Raiser's Edge NXT® Online Giving EAP
- Diocesan Blackbaud Raiser’s Edge NXT® User’s Group
- 2 Blackbaud Consultant’s Community
- 43 End of Term Grade Entry EAP
- 92 EAP for Query in Blackbaud Raiser's Edge NXT®
- 38 Standard Reports for Blackbaud Raiser's Edge NXT® EAP
- 12 Payments Assistant for Blackbaud Financial Edge NXT® EAP
- 6 Ask an All Star (Austen Brown)
- 8 Ask an All-Star Alex Wong (Blackbaud Raiser's Edge NXT®)
- 1 Ask an All-Star Alex Wong (Blackbaud Financial Edge NXT®)
- 6 Ask an All-Star (Christine Robertson)
- 21 Ask an Expert (Anthony Gallo)
- Blackbaud Francophone Group
- 22 Ask an Expert (David Springer)
- 4 Raiser's Edge NXT PowerUp Challenge #1 (Query)
- 6 Ask an All-Star Sunshine Reinken Watson and Carlene Johnson
- 4 Raiser's Edge NXT PowerUp Challenge: Events
- 14 Ask an All-Star (Elizabeth Johnson)
- 7 Ask an Expert (Stephen Churchill)
- 2025 ARCHIVED FORUM POSTS
- 322 ARCHIVED | Financial Edge® Tips and Tricks
- 164 ARCHIVED | Raiser's Edge® Blog
- 300 ARCHIVED | Raiser's Edge® Blog
- 441 ARCHIVED | Blackbaud Altru® Tips and Tricks
- 66 ARCHIVED | Blackbaud NetCommunity™ Blog
- 211 ARCHIVED | Blackbaud Target Analytics® Tips and Tricks
- 47 Blackbaud CRM Higher Ed Product Advisory Group (HE PAG)
- Luminate CRM DC Users Group
- 225 ARCHIVED | Blackbaud eTapestry® Tips and Tricks
- 1 Blackbaud eTapestry® Know How Blog
- 19 Blackbaud CRM Product Advisory Group (BBCRM PAG)
- 1 Blackbaud K-12 Education Solutions™ Blog
- 280 ARCHIVED | Mixed Community Announcements
- 3 ARCHIVED | Blackbaud Corporations™ & Blackbaud Foundations™ Hosting Status
- 1 npEngage
- 24 ARCHIVED | K-12 Announcements
- 15 ARCHIVED | FIMS Host*Net Hosting Status
- 23 ARCHIVED | Blackbaud Outcomes & Online Applications (IGAM) Hosting Status
- 22 ARCHIVED | Blackbaud DonorCentral Hosting Status
- 14 ARCHIVED | Blackbaud Grantmaking™ UK Hosting Status
- 117 ARCHIVED | Blackbaud CRM™ and Blackbaud Internet Solutions™ Announcements
- 50 Blackbaud NetCommunity™ Blog
- 169 ARCHIVED | Blackbaud Grantmaking™ Tips and Tricks
- Advocacy DC Users Group
- 718 Community News
- Blackbaud Altru® Hosting Status
- 104 ARCHIVED | Member Spotlight
- 145 ARCHIVED | Hosting Blog
- 149 JustGiving® from Blackbaud® Blog
- 97 ARCHIVED | bbcon® Blogs
- 19 ARCHIVED | Blackbaud Luminate CRM™ Announcements
- 161 Luminate Advocacy News
- 187 Organizational Best Practices Blog
- 67 everydayhero Blog
- 52 Blackbaud SKY® Reporting Announcements
- 17 ARCHIVED | Blackbaud SKY® Reporting for K-12 Announcements
- 3 Luminate Online Product Advisory Group (LO PAG)
- 81 ARCHIVED | JustGiving® from Blackbaud® Tips and Tricks
- 1 ARCHIVED | K-12 Conference Blog
- Blackbaud Church Management™ Announcements
- ARCHIVED | Blackbaud Award Management™ and Blackbaud Stewardship Management™ Announcements
- 1 Blackbaud Peer-to-Peer Fundraising™, Powered by JustGiving® Blogs
- 39 Tips, Tricks, and Timesavers!
- 56 Blackbaud Church Management™ Resources
- 154 Blackbaud Church Management™ Announcements
- 1 ARCHIVED | Blackbaud Church Management™ Tips and Tricks
- 11 ARCHIVED | Blackbaud Higher Education Solutions™ Announcements
- 7 ARCHIVED | Blackbaud Guided Fundraising™ Blog
- 2 Blackbaud Fundraiser Performance Management™ Blog
- 9 Foundations Events and Content
- 14 ARCHIVED | Blog Posts
- 2 ARCHIVED | Blackbaud FIMS™ Announcement and Tips
- 59 Blackbaud Partner Announcements
- 10 ARCHIVED | Blackbaud Impact Edge™ EAP Blogs
- 1 Community Help Blogs
- Diocesan Blackbaud Raiser’s Edge NXT® Users' Group
- Blackbaud Consultant’s Community
- Blackbaud Francophone Group
- 1 BLOG ARCHIVE CATEGORY
- Blackbaud Community™ Discussions
- 8.3K Blackbaud Luminate Online® & Blackbaud TeamRaiser® Discussions
- 5.7K Jobs Board