How to compare data in Excel 2010

**Dawud Beale** · 09-11-2013, 06:42 AM

Hi everyone.

I have some data I need to compare in Excel 2010.

It looks something like this:

1 2 1 2 1 2
1 2 1 5 1 2
1 2 1 2 1 2
1 8 1 2 9 2
1 2 1 2 1 2
1 2 4 2 1 2
1 2 1 2 1 2

and it needs to be compared with:

1 2
1 2
1 2
1 2
1 2
1 2
1 2

These same two columns should be used to compare all 6 columns in the first piece of data. The 1 columns should be compared and the 2 columns should be compared.

As an output I need a list of all cells that don't match what they are being compared with.

Any suggestions on how I might achieve this?

**stanleydgromjr** · 09-11-2013, 07:15 AM

Cross posted http://www.mrexcel.com/forum/excel-q...el-2010-a.html

Please read Excelguru Help Site - http://www.excelguru.ca/content.php?184

**Dawud Beale** · 09-11-2013, 07:23 AM

Oh sorry I've never heard of cross posting before. I was just looking to get an answer and wasn't sure I'd definitely get one here.

In future I will stick to one and if I don't get a reply after a few days I will post somewhere else but will include the initial link.

Thanks for the information

**millz** · 09-11-2013, 07:38 AM

No answers on the cross post yet.

Maybe elaborate more on your requirements? My brain hurts reading this sentence...

The 1 columns should be compared and the 2 columns should be compared.

and, most preferably, post also an example of how the results should look like, based on your "sample" data in the first post.

**Andy Pope** · 09-11-2013, 07:38 AM

Not 100% sure what you expect in terms of output but this builds a table of TRUE/FALSE if the values match.

There is an example using the 2 column tables as values and an example where 1 and 2 are derived.

**Dawud Beale** · 09-11-2013, 08:54 AM

What Andy has done is basically what I was looking to do. Except leaving it blank when the data matches and displaying the value of the data when it doesn't match, rather than displaying true or false.

I am now trying to make a count of the total errors in the data array.

As the cell is blank when the data matches, I tried =COUNTIF(AV3:CA98, "*")but it seems to count a blank space i.e. " ". How could I make a count that only includes data that doesnt match the sample data?

**Andy Pope** · 09-11-2013, 08:58 AM

I1: =IF(A1=INDEX($A$12:$B$18,ROW(),MOD(COLUMN()-COLUMN($I$1),2)+1),"",A1)

and this to report 4 mis matches
=COUNT(I1:N7)

**Dawud Beale** · 09-11-2013, 09:02 AM

Oh thats a little complicated. How do i adjust that formula to work on my spreadsheet?

I put it into the experimental spreadsheet you made for me and it doesnt do anything. Any advice?

**Andy Pope** · 09-11-2013, 09:09 AM

Really?

Original formula
=A1=INDEX($A$12:$B$18,ROW(),MOD(COLUMN()-COLUMN($I$1),2)+1)

updated formula
=IF(A1=INDEX($A$12:$B$18,ROW(),MOD(COLUMN()-COLUMN($I$1),2)+1),"",A1)

a few characters added to start and finish

**Dawud Beale** · 09-11-2013, 09:12 AM

That just comes out blank as a blank result on my spreadsheet

**Dawud Beale** · 09-11-2013, 09:18 AM

Ok i used this formula to compare the real data with the sample data:

=IF(K3=$AS3,"",K3)

For some reason though, when I use this formula on 189 cells:

=COUNTIF(BQ15:BY35, "*")

The value is 189. Even though the vast majority of the cells are blank.

They are blank because the samlpe data matches the real data

**Andy Pope** · 09-11-2013, 09:23 AM

Although I never explicitly said I assume you entered the formula and then copied over and down the rest of the cells in the table, so range I1:N7

The wildcard will cause any cell in that range that contains a formula to be counted.

Assume the return values are "" or number use
=COUNT(BQ15:BY35)

**Dawud Beale** · 09-11-2013, 09:28 AM

Is this on the sample spreadsheet you made? you already have a formula there so wont it copy over the top of it?

How do I make this formula look at the end result of the formula of the cell its looking in rather than the formula itself?

=COUNTIF(BQ15:BY35, "*")

**Andy Pope** · 09-11-2013, 09:33 AM

Yes in the sample file and yes it will. But that is okay because you wanted the formula to return different results to the ones I had.

basically what I was looking to do. Except leaving it blank when the data matches and displaying the value of the data when it doesn't match, rather than displaying true or false

**Dawud Beale** · 09-11-2013, 09:37 AM

Oh sorry i apologise we are talking about different things. ok I am with you now. Ah so basically how to leave it blank if the data matches.

Ok so now how do I get excel to count the number of non matching data?

**Dawud Beale** · 09-11-2013, 09:40 AM

I just tried your updated formula and it works. I need a way to count the number of items of data rather than the sum of the value of the data though. Also the data I actually have is hexadecimal and has an 0x at the begining so may be something like 0xFF or something. So it cant be counted numerically. I need to count the number of entries that don't match the original data

**Andy Pope** · 09-11-2013, 09:51 AM

two ways to count.

Array formula, commit using CTRL+SHIFT+ENTER,

=SUM(IF(I1:N7<>"",1,0))

or normal formula

=COUNTA(I1:N7)-COUNTBLANK(I1:N7)

**Dawud Beale** · 09-11-2013, 10:06 AM

I still cant get those formulas to work on mine for some reason, even though they work on yours. Any ideas why?

**Andy Pope** · 09-11-2013, 10:07 AM

not without seeing yours.

**stanleydgromjr** · 09-11-2013, 08:28 PM

Dawud Beale,

See your posted thread on MrExcel for screenshots and a macro, by me, hiker95 on MrExcel.

http://www.mrexcel.com/forum/excel-q...el-2010-a.html

**Dawud Beale** · 09-12-2013, 06:32 AM

Thanks for all the help everyone. Its working.

Just as a final tool that I'd like to have, is it possible to get excel to list all the errors in 2 columns?

a 5555 column containing all the erroneous 5555 data:

555a
0505
55f5

etc

and an AAAA column containing all the erroneous AAAA data:

AAA0
AABA
FAAA
A9A9

etc

If there is a way to do this, it will be excellent

How to compare data in Excel 2010

LinkBack

Thread Tools

Rate This Thread

Display

How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Re: How to compare data in Excel 2010

Thread Information

Users Browsing this Thread

Similar Threads

Using DAO to pull data from Access 2010 into an Excel 2010 table

Importing data from a Word 2010 form to Excel 2010.

Importing data from an Excel 2010 worksheet into a Word 2010 form.

Excel 2010 Macro Needed for Search, Compare & Change

Excel 2010: compare 2 sheets, replace a value in a cell

Bookmarks

Bookmarks

Posting Permissions