CODEWITHSUNDEEP
excel
Ryan Shukis
Ryan Shukis

Reputation: 1

How can I remove non-matching values in two different columns and sort in Excel?

I have several columns of data in my Excel spreadsheet.

Originally, I had two different spreadsheets, as they were generated from reports in a software application.

One of the spreadsheets contains the names of individuals who have had transactions with us in the past year. The other spreadsheet contains the names and the phone numbers. I copied and pasted the columns with the names and phone numbers into my spreadsheet with just the names of people who have purchased something from us in the past year.

My ultimate goal is to extract the names and phone numbers of only the names that have purchased something in the past year.

My column for the past year contains 1,002 names, while my master customer list (with phone numbers) contains over 20,000 individuals. I need the phone numbers of all of the individuals that have purchased something from us in the past year, but I don't want to have to manually go through 1,000 names (and, essentially, 20,000+ to find the match).

If I can achieve my goal without having to use VBA, that would be great. If this is the only route I can take, then I will go that route, but I would like to avoid coding if possible. (This is simply due to time constraints.)

Upvotes: 0

Views: 7139

Answers (1)

gknicker
gknicker

Reputation: 5569

The VLOOKUP function is likely the best solution for you. From the Excel documentation, it:

Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in an ascending order.

Note well the implication of that last sentence: the column you're searching in (leftmost column of the lookup table) must be sorted in ascending order for this function to produce the correct results.

Taking a simple example, let's say you have Sheet1 in your Excel workbook with the following information:

     A           B           C
1  Name    Transactions   Phone
2  Sally              3
3  Alice              5
4  Joe                2
5  Jon                1

You need to add their phone numbers to this sheet, from another workbook. Let's say your phone number information in the other workbook looks like this:

     A        B
1  Name    Phone
2  Alice   2222222
3  Bill    3333333
4  Bob     4444444
5  Jim     5555555
6  Joe     6666666
7  Sally   7777777
8  Sue     8888888
9  Tom     9999999

Take the following steps to add the phone numbers to Sheet1 in the first workbook:

  1. Copy the phone information into a blank sheet in the first workbook. Let's call this Sheet2 for this example.
  2. Make sure the phone information is sorted ascending by the Name column (A), because that's the leftmost column and thus the lookup column.
  3. In cell C2 of Sheet1 (the empty phone cell for Sally), enter: =VLOOKUP(A2, Sheet2!A$2:B$9, 2,FALSE).
  4. Drag-copy this formula down to the remaining cells in the Phone column.

Result:

     A           B           C
1  Name    Transactions   Phone
2  Sally              3   7777777
3  Alice              5   2222222
4  Joe                2   6666666
5  Jon                1    #N/A

Notes:

  • The second parameter (Table_array - the lookup data range) should not include the column headings. As you can see, it's Sheet2!A$2:B$9 so it includes the information from rows 2 to 9 in columns A and B.
  • The last parameter (Range_lookup) should be set to FALSE so you don't pick up the information from the closest match. Note how Jon has no matching phone number record, so his Phone is set to "#N/A" - otherwise he would have been assigned Joe's phone number since that's closest match to Jon.

Parameter documentation:

  1. Lookup_value is the value to be found in the first column of the table, and can be a value, a reference, or a text string.
  2. Table_array is a table of text, numbers, or logical values, in which data is retrieved. Table_array can be a reference to a range or a range name.
  3. Col_index_num is the column number in Table_array from which the matching value should be returned. The first column of values in the table is column 1.
  4. Range_lookup is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE.

Upvotes: 1

Related Questions