[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

excel compare to columns

Posted on 2011-10-04
12
Medium Priority
?
357 Views
Last Modified: 2012-08-13
Hi,

I have 2 columns in excel they both data in it but i need to find the differences and row numbers because one colums has 200 more records, they are not ordered in any particualr way either

Please help

Thanks,

R8VI
0
Comment
Question by:R8VI
  • 4
  • 4
  • 3
  • +1
12 Comments
 
LVL 5

Expert Comment

by:magento
ID: 36910525
Please send the sample i/p and o/p?
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36910539
assuming that the 2 columns are column A and column B, easiest way would be to sort both columns (one at a time) and to type the following formula in cell C1 :
=IF(A1<>B1, "difference", "")

Open in new window


you can then multiply the formula by dragging it downwards and apply a filter to hide all empty cells in the C column
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36910562
pay attention though that this approach will only give you the answers you need if entries in the rows are related to each other. When both columns are identical but one cell is missing, you will get a lot of differences !

if you could post an example sheet, we could come up with an answer suitable to your situation.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:R8VI
ID: 36910607
Hi,

Sorting is really not an option because there are about 3000 plus records and example data

1974635      3438418
3438418      3592898
3592898      7138356
7138356      7310161
7141481      7771217
7310161      1974635

on this data there is only 1 records that match both coloums I need to find all the differences

Thanks,

R8VI
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36910656
Hello R8VI,
Take a look at the example workbook.

It uses the following formula

=IF(ISERROR(MATCH(D5,MyRange,0)),"No Match",ROW(OFFSET(MyRange,0,0,1,1))+MATCH(D5,MyRange,0)-1)

Open in new window


The range MyRange is the shorter list.  The formula will return "No Match" is the item is not found in both lists.  Otherwise it will return the row number of the first match in the second list.

Kyle
Q-27379213-RevA.xlsx
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36910774
So for given example you would like to get all records on the left except for the 3438418 entry ?
0
 
LVL 19

Expert Comment

by:Arno Koster
ID: 36910792
then use kgerb's function and filter all entries to show only those containing "No Match"
0
 

Author Comment

by:R8VI
ID: 36910863
Hi kgerb's

I have tried your example but still i dont think its right this is how I have changed it to


=IF(ISERROR(MATCH(C2,B2:B3908,0)),"No Match",ROW(OFFSET(B2:B3908,0,0,1,1))+MATCH(C2,B2:B3908,0)-1)

but I am getting more than 1000 records that dont match

when the big list has 3908 records and small list has 3642 records so there should only be about 260 odd that dont match or not there

and need to identfiy which row or which ones they are

Thanks,

R8VI
0
 

Author Comment

by:R8VI
ID: 36910905
Hi,

I looked at the first record in C  and it says no match
the record is 345444 I did a control find in B and its the 6 one down

Please help

Thanks,

R8VI
0
 
LVL 12

Expert Comment

by:kgerb
ID: 36910989
I think the problem might be that you need to reference your short list absolutely.  Change B2:B3908 to $B$2:$B$3908.  If that doesn't work I'll need to see some data.  It's hard to know why the match function is not working correctly without seeing the actual data.  Can you paste the two columns of data into a new workbook and upload it?  

Also, my formula returns the row of the first match found in the short list.  Is that not what you want?  I attached a new workbook with the data you provided.

Kyle
Q-27379213-RevB.xlsx
0
 

Author Comment

by:R8VI
ID: 36911268
Hi Kyle,

I am not sure if this is what your solution does.

But what I want it that take everything from A and match To B and bring back row number or records that do not appear in B which is the smaller one

Thanks,

R8VI
0
 
LVL 12

Accepted Solution

by:
kgerb earned 2000 total points
ID: 36911470
Ok, let's try this again.  Select the short list (column B I assume) from the first data entry to the last and name this range "MyRange".

In column C put the following formula:
=IF(ISERROR(MATCH(B3,MyRange,0)),ROW(B3),"")

Open in new window

This will indicate which values in column A are not found in column B.  Drag the formula all the way down to the bottom of the short list.  Name this range "BlanksRange" because the numbers are separated by blank cells.

Create another named range in column D right next to "BlanksRange" named "NoBlanksRange".  This range must be the same size as "BlanksRange".

In the first cell of "NoBlanksRange" put the following formula:
=IF(ROW()-ROW(NoBlanksRange)+1>ROWS(BlanksRange)-COUNTBLANK(BlanksRange),"",INDIRECT(ADDRESS(SMALL((IF(BlanksRange<>"",ROW(BlanksRange),ROW()+ROWS(BlanksRange))),ROW()-ROW(NoBlanksRange)+1),COLUMN(BlanksRange),4)))

Open in new window

NOTE:  THIS IS AN ARRAY FORMULA!!!!  You need to press CTRL+SHIFT+ENTER after you paste it into the cell.

Drag this formula all the way to the bottom of "NoBlanksRange".  It will list all the rows where no matches occur without the blanks cells.

I think this will get us where we want to be:).

I have attached a file for your reference as well as some pictures.

Kyle

 pic1 pic2 pic3Q-27379213-RevC.xlsx
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question