Microsoft Excel - How often do you use it?

[Coach]

For advanced users, tell us some of your tricks in terms of using the formulas and such.

For beginners, ask questions here, hopefully answers could be provided.

As for me, my question to the experts/advanced users,

Let's say if I have a spreadsheet with column A that has names, typically last name, first name and middle initial (example: Smith, Joe Steve). Column B has names that is typically the same as column A, except it would be missing the middle name or a misspelling on the first name (example: Smith, Joe).


Column A would have say 450 names and Column B would have 150. How can a "near" match search be found, after utilizing the conditional formatting that highlights duplicate values?

[scho63]

There are some pretty cool plug ins you can find for Excel if you search hard enough.

I used a plug in tool from Able Bits called duplicate finder that helped me clean up data tables in Salesforce from massive duplication of leads/contacts and other data issues. Worked great.

Now there are so many videos on Youtube and teaching series, it's pretty easy to resolve your issues or learn new tricks.

[Mr. Plow]

I'm just here for the formulas

[Buehler445]

Originally Posted by SithCeNtZ

I don't know anything about your company obviously, but storage and processing power is so cheap now that you could probably build a DB for free in something like AWS on their free tier. You'd have to learn how to use it of course, but don't let cost be a reason you don't do it.

Time is the issue. Because it is me. And fat boy has shit to do.

[CapsLockKey]

Originally Posted by SithCeNtZ

I don't know anything about your company obviously, but storage and processing power is so cheap now that you could probably build a DB for free in something like AWS on their free tier. You'd have to learn how to use it of course, but don't let cost be a reason you don't do it.

Can do some real powerful stuff in Excel when you start using external data connections. I used to use Access religiously but most people aren't familiar with it. I started using Excel connecting to live data with parameters and it blows people's minds when I send them a spreadsheet and tell them it auto updates everytime they open it.

[DanT]

I would set up a third table, ideally on it's own tab , that would have one row for each spelling that appears in either of the two lists you are trying to match. That row would have at least two columns, one with the spelling and the other with a unique code for the person the spelling belongs to. (You might want to use separate columns for first and last names.) This would be a many- to-one mapping, of course. The code could be numeric, text or both, as long as it is unique. Then, matching the two lists is easy
You just use the third table to resolve each spelling to a unique code that can be used for matching, perhaps via VLOOKUP(). You may want to learn about SOUNDEX, which is one way to help get many-to-one mappings of names.

The "third table" will need to be updated with new spellings, but that can be done as a matter of maintenance.

[Coach]

@kccrow

Okay, so I modified some of the ideas/suggestions you provided, and it's a good start. However, I am still running into some issues.

So as you can see, Column C is the Verification. It's still showing individuals who are on the roster, even though some of them on the left is not on the right as evidenced on the conditional formatting for duplicates (yellow). I'm using the conditional formatting on Column A and Column E to easily identify the correct name layout. No issues there.

I'm also using conditional formatting on Column B and Column F to identify a match of shortened names, which will just require a quick manual verification to see if it's the same individual based off of the long name. No issues there.

The issue I'm having is the Column C. The last 4 (Column A & B, rows 8 to 11) are definitely not on the roster on Column D and/or E, yet it's showing they're on the roster. Why is that? I attached the photo with the formatting showing. Additionally, if I were to change the $F$2:$F$17 format to $F:$F (to capture all of column F as it will grow in over time), how can that work?

Thanks

[kccrow]

Originally Posted by Coach

@kccrow

Okay, so I modified some of the ideas/suggestions you provided, and it's a good start. However, I am still running into some issues.

So as you can see, Column C is the Verification. It's still showing individuals who are on the roster, even though some of them on the left is not on the right as evidenced on the conditional formatting for duplicates (yellow). I'm using the conditional formatting on Column A and Column E to easily identify the correct name layout. No issues there.

I'm also using conditional formatting on Column B and Column F to identify a match of shortened names, which will just require a quick manual verification to see if it's the same individual based off of the long name. No issues there.

The issue I'm having is the Column C. The last 4 (Column A & B, rows 8 to 11) are definitely not on the roster on Column D and/or E, yet it's showing they're on the roster. Why is that? I attached the photo with the formatting showing. Additionally, if I were to change the $F$2:$F$17 format to $F:$F (to capture all of column F as it will grow in over time), how can that work?

Thanks

I'll take a peek at it here in short order and let you know.

As for your last statement, growing is usually done by a statement starting in the first row as $F$2:$F2. As you copy down, that formula will grow by row to $F$2:$F3, $F$2:$F4, and so on.

Edit: And yes, you can capture an entire column with the formula you stated but it will always be all inclusive. It's great for counting things but not so great for some other things, such as depreciating values of some kind. For instance, I have a spreadsheet that will calculate dead cap space on contracts remaining by year. I set it as an opposite of what I stated above, as SUM(F2:$F$5) for instance. As I fill down, it will sum less and less of the column.

[kccrow]

Put this in Column C and see if it does what you want:

=IF(IFERROR(MATCH(IFERROR(CONCATENATE(TEXT(LEFT(A2,FIND(",",A2)-1),""),", ",TEXT(LEFT(MID(A2,FIND(" ",A2)+1,256),1),"")),""),INDIRECT(CONCATENATE("$E$2:",ADDRESS(COUNTA($E:$E),5))),0),0)<>0,"On Roster","Not On Roster")


Basically, I substituted the $E$2:$E16 portion of the original formula with INDIRECT(CONCATENATE("$E$2:",ADDRESS(COUNTA($E:$E),5)))

What that is doing is counting the total number of non-blank cells in column E, then using that # as a row reference in the ADDRESS function along with Column 5 (which is E) to return an address relative to the number of entries in column E, such as $E$16 when there are 15 entries. I then CONCATENATE that with $E$2 to arrive at a RANGE reference of $E$2:$E$16 which the INDIRECT function uses to return the value at the location originally referenced.

[kccrow]

If you want it a tad cleaner, and to show a blank in C when A is blank, use this:

=IF(IFERROR(MATCH(IFERROR(CONCATENATE(TEXT(LEFT(A11,FIND(",",A11)-1),""),", ",TEXT(LEFT(MID(A11,FIND(" ",A11)+1,256),1),"")),""),INDIRECT(CONCATENATE("$E$2:",ADDRESS(COUNTA($E:$E),5))),0),0)<>0,"On Roster",IF(A11="","","Not On Roster"))

[kccrow]

Also, keep in mind that if you move things around you have to pay attention to the reference cells in that formula because my entire substitution will not change with inserts of columns... It's basically a hard code. If you change the location from column E to something else, not only do you have to change the letter E to whatever column, but you have to change the #5 in the address portion to whatever column number that is.

[kccrow]

If you want to get all funky... and serious...

Insert a new column D and label it Roster Reference

In column C change the formula to:
=IF(IFERROR(MATCH(IFERROR(CONCATENATE(TEXT(LEFT(A2,FIND(",",A2)-1),""),", ",TEXT(LEFT(MID(A2,FIND(" ",A2)+1,256),1),"")),""),INDIRECT(CONCATENATE("$F$2:",ADDRESS(COUNTA($F:$F),6))),0),0)<>0,"On Roster",IF(A2="","","Not On Roster"))

In column D put a formula as:
=IFERROR(ADDRESS(MATCH($B2,$F:$F,0),5),"No Reference")

This will give you a sheet that not only verifies that they are on the roster, but will tell you exactly where on the sheet.

It would look like this:

[cooper barrett]

Originally Posted by Discuss Thrower

It's been said that there is more fiction created in one day in Microsoft Excel than there will ever be created in Microsoft Word.

[Coach]

Originally Posted by kccrow

Put this in Column C and see if it does what you want:

=IF(IFERROR(MATCH(IFERROR(CONCATENATE(TEXT(LEFT(A2,FIND(",",A2)-1),""),", ",TEXT(LEFT(MID(A2,FIND(" ",A2)+1,256),1),"")),""),INDIRECT(CONCATENATE("$E$2:",ADDRESS(COUNTA($E:$E),5))),0),0)<>0,"On Roster","Not On Roster")


Basically, I substituted the $E$2:$E16 portion of the original formula with INDIRECT(CONCATENATE("$E$2:",ADDRESS(COUNTA($E:$E),5)))

What that is doing is counting the total number of non-blank cells in column E, then using that # as a row reference in the ADDRESS function along with Column 5 (which is E) to return an address relative to the number of entries in column E, such as $E$16 when there are 15 entries. I then CONCATENATE that with $E$2 to arrive at a RANGE reference of $E$2:$E$16 which the INDIRECT function uses to return the value at the location originally referenced.

I placed the above text you gave me on Column C, row 2, and dragged it down. This is the result I'm getting....

I must be missing something, obviously, so it's on my end.

[Coach]

kccrow,

I'm wondering if it might be easier to email you this document and let you figure it? Obviously, you're only able to see just a portion of it and not the whole puzzle.

[kccrow]

Originally Posted by Coach

I placed the above text you gave me on Column C, row 2, and dragged it down. This is the result I'm getting....

I must be missing something, obviously, so it's on my end.

Let me see if I can put something together that will explain all the formulas. Also, I think you did not alter the formula when you inserted column D as I had outlined, which is why you're getting that particular outcome.