Search: | |||||

| ||||

Home » Forum » MS Office | RSS Feeds |

I have 2 columns with over 30000 records in column A and 23889 in B

I would like to sort and match A and B and exclude the ones that do

not match.

Any help???

1. Insert a new column between A & B (new column B; old column B becomes C)

2. In first row of data (row 1 in my example), enter Vlookup formula in column

B.

B1 =VLOOKUP(A1,C:C,1,FALSE)

3. Copy formula down for as many rows as have data in column A.

4. In first row of data (1), enter Vlookup formula in column D.

D1 =VLOOKUP(C1,A:A,1,FALSE)

5. Copy formula down for as many rows as have data in column C.

The cells in columns B & D which returned #N/A have no matching value.

6. Convert columns B & D to values (Copy, then Paste Special as Values)

7. Select columns A & B. Sort by column B first and A second (using Sort from

the Data menu).

8. Copy the cells in columns A & B where B contains #N/A. Paste them on

another sheet if you want to keep a list of them. Delete the contents of those

cells (not rows) on your original sheet.

9. Repeat steps 7 & 8 for columns C & D.

10. Delete columns B & D.

Columns A & B should now have matching values in all rows. Records with no

match are on the other sheet (if you copied & pasted them there).

May this help you

Enter following formula in C1

=if(countif(A$1:A$30000,C1)>1,"y","n")

Copy the same uuto C30000

Select c1:c30000

Select copy

Select paste special

Select values

Sort entire data on column c

Didn't find what you were looking for?
Find more on sort unmatched cells
Or get search suggestion and latest updates.

Related Topics:

- Retain cell selection through sort
- Sort where a selected cell is preserved
- Copying current cell's value to another cell
- Sort the Sheet & Sort or Filter Columns
- Sort an array (Bubble sort)
- Sort an array (Bubble sort) - Signed
- Write a C program that shall perform sort 10 numbers using insertion sort and stack operation.
- how are cell coordinates referenced on a clicked cell
- to see if text in a cell in a column is contained in any cell
- Moving data in the last cell of row to another column cell
- Action taken by Excel when a condition causes a cell's value
- Function needed to split the content of a cell intomany cells
- Function needed to split the content of a cell into many cells
- Finding and replacing the cell next to a cell with a certain string
- Copy Value produced by formula in cell C3 to cell D3
- Update a cell based on a DDE updated cell value
- active cell no longer advances to cell below
- A Paragraphs in one cells --> convert into a line in per cells
- Cell validation based on the contents of previous cell
- Copy cell contents to same cell on different worksheet if contains
- How to copy automaticly (..using VB) my active cell after cell entry in different cell in anoth
- how to sort column by number of characters per cell ?
- datagrid sorting
- Sort a directory on creationdate
- Multi-Column Sorting