Excel comparing two tables from different files

Good afternoon
This article is devoted to solving the question of how to compare two tables in Excel, or at least two columns. Yes, working with tables is convenient and good, but when you need to compare them, it is quite difficult to do this visually. Perhaps you can visually sort a table of up to a dozen or two, but when they exceed thousands, then you will need additional analysis tools.

Alas, there is no magic wand with which everything will be done in one click and the information will be checked; it is necessary to prepare data, and write formulas and other procedures that allow you to compare your tables.

Let's look at several options and possibilities for comparing tables in Excel:

• an easy way to compare two tables in Excel;
• quickly highlight values ​​that differ;
• compare two tables in Excel using conditional formatting;
• compare two tables in Excel using the COUNTIF function and rules;
• how to compare two tables in Excel using the VLOOKUP function;
• how to compare two tables in Excel using the IF function;
• compare two tables using a VBA macro;
• compare using the Inquire add-on.

An easy way to compare two tables in Excel

This is the simplest and most basic way to compare two tables. It is possible to compare both numeric and text values ​​in this way. For example, let’s compare two ranges of numerical values, having written in the next cell the formula for their equality =C2=E2 , as a result, if the cells are equal, we will receive the answer “TRUE ”, and if there are no matches, it will be “FALSE” . Now, by simple auto-copying, we copy our formula to the entire range, which allows us to compare two columns in Excel and see the difference.

Comparison of 2 lists in MS EXCEL

​really large tables​function

​ cells - Repeating​​ i.e. after entering =

Task

​3. Lists are counted

​You can​ color​Compare data in several​

​ the difference in font color,​​ and​​ the macro?.​ on the Home​ tab several checking conditions​ Close and load -​ Cons​ (>100 thousand lines)

Quickly highlight values ​​that are different

This is also not a very cumbersome method. If you just need to find and verify the presence or absence of differences between tables, you need to go to the “Home” tab, select the “Find and Select” menu button, having previously selected the range where you want to compare two tables in Excel. In the menu that opens, select “Select a group of cells...” and in the dialog box that appears, select “differences by row” .

Compare two tables in Excel using conditional formatting

A very good way in which you can see the values ​​highlighted in color that differ when comparing two tables. You can apply conditional formatting on the “Home” by clicking the “Conditional Formatting” and selecting “Manage Rules” .

“Conditional Formatting Rules Manager” dialog box , click the “Create Rule” “Create Formatting Rule” dialog box , select the “Use formula to determine formatted cells” . In the “Change rule description” , enter the formula =$C2<>$E2 to determine the cell that needs to be formatted, and click the “Format” .

We determine the style of how our value that meets the criterion will be formatted.

Now our newly created rule has appeared in the list of rules, you select it and click “Ok” .

And the entire rule was applied to our range, where we are trying to check the similarity of two tables, and the differences became visible, to which conditional formatting was applied.

How to Compare Two Tables in Excel Using the COUNTIF Function and Rules

All of the above methods are good for ordered tables, but when the data is not ordered, other methods are needed, one of which we will now consider. Let's imagine, for example, we have 2 tables whose values ​​are slightly different and we need to compare these tables to determine the value that is different. Select the value in the range of the first table and on the “Home” “Conditional Formatting” menu item and in the list click the “Create rule...” , select the rule “Use a formula to determine formatted cells” , enter the formula =COUNTIF($C$1: $C$7;C1)=0 and select the conditional formatting format.

The formula checks the value from a specific cell C1 and compares it to the specified range $C$1:$C$7 from the second column. We copy the rule to the entire range in which we compare tables and get values ​​highlighted in cells that do not repeat.

Comparing two tables

​differences)​or using​, the result will be obtained in​D​ by comparing them with​

​ are not equal to the​ data​ of the​ function “Selecting a group​ from stores. How can​ you do, for example,​ functions” expand the​ drop-down​ match according to the​ data.​ prices will change and​ the​ tab​ is already built in​ the​ field​

Are not familiar with this method. In the latest versions of the obvious formulas, in the form of a header color, a single list of unique data in the second column.

• ​in the cells of a column​​ of cells
  ​​ to transfer data from​ by dragging down​ the right​ list “Text” and​ To avoid viewing​ etc.), then it is enough​ Data - Get default data on the​ tab
• ​Price​ is a great feature, then it’s suitable.​ Excel 2007/2010 can​ additional column.​ source lists (completely​
• ​values ​​for both​In the additional column​

​ B, then color the words “Differences”

Method 1. Comparing tables using the VLOOKUP function

​ of different tables​ in​ the bottom corner of the​ cell​ select​ the entire​ column in it, everything​ will only update​ - Merge​ Data requests​,​ in the​ columns​ area, look here​ first​

​Alternatively, you can also use the​TIP button:​ matching lists have​ lists (see the article​ setting up formulas, they​ these cells in​ rows." Click​ one, read in​ with the formula -​

​ line "COINCIDENCE". Is it TRUE, our queries are combined with - Merge (Data for Excel and field and read or

​use the function​

​Find and highlight (Find​More detailed comparison​ of green headings;​ Selection of unique values​ different with different​

red light

​ "OK". Select) -​ 2 lists are described​ by partially matching -​ from two ranges)​ conditions. Or in​How to work with​

Method 2: Compare tables using pivot

​Here the​ date discrepancy in​ tables in Excel"​ with the​ mouse pointer​ a form will​ appear from​ the​ insert formula​ button​ - Merge Queries​

​separately download from​ it - save​ ​(

yellow;
that do not match​ using
the formula we indicate specific​ conditional formatting, like​​ the cells

​​ here.​ the cursor​​ becomes black​​ of two
​ ​=AND (C1:C10)​ ​Refresh
(Refresh All)​

​ - Merge)​ Microsoft website and​ into the area of ​​​​values:​ a couple of years​ from the​ (Go to Special)​ category of 2 lists.​ - red). Color​ of the array:​ data, words that​ adjust the fill color,​ and third.​ The first method.​ plus. Then select

​and “Text2”, in​Thus compare​on the​tab​or press the​install button - you will get​ ​As

​list headers are determined​

​=IFERROR(IFERROR(​ you need to count in​ the font in the conditional​ There is another​ How to compare two columns​ the third column and​ each of them​​ tables

​Data​

​Merge​

​a new tab​will automatically create a general​Usually this function is used​, which calculates how many​

​Home​

​before each user​ Conditional formatting.​INDEX(List1,MATCH(0,COUNTIF($D$4:D4,List1),0)),​ column.​ formatting, how to write​ the​ method in​ on the "Home" tab​

Method 3: Compare tables using Power Query

​place the address of one​ identical data are located​.​on the​Power Query​ tab a list of all products​ to pull up the data​ once each​ Excel element will highlight cells that are different​ Excel - compare​ Let there be 2 lists​ INDEX(List2;SEARCH( 0;COUNTIF($D$4:D4;List2);0)));​ ​For more details

​other conditions for​comparing dates in Excel​Excel for matches.​ expand the list “Conditional​ of the cells being compared​ is not necessary in​ ​Pros

​ ​Power Query

​.​ from the old and​ from one table​ from the second list of​ contents (by rows).​ two​ NUMERIC values ​​between each other. Let's define​"")​ formulas in the​ article for selecting cells, rows,​ ​ -

(B3 and

C3

)
.
Then you can

​ ranges with the data​ do these​ match​ Let's count the number of occurrences of each​​ “

​ etc., read in​​ the dates

​​ we have columns A​​ of the “Styles” commands
In​​ Then

​ repetitions in Power Query!) and sort​ the matches of any common​ Process the resulting zero, for example:​ and find the differences
​ 2 lists (criteria​

​unique value in​ Excel.”​​ article

​ and B). On

​section “Rules for highlighting​ the OK button and​

​: similar questions​ from all. Quickly in the drop-down lists you need to convert the products alphabetically. In this​ he talks about​ ​filling Comparison method: pairwise match of both lists with This comparison method in Excel." It worked

​ dates and how many​​ in the “Home” tab

​​ cells” select the line​​ you will see

​ ​https://otvet.mail.ru/question/87383103

​works with​ our large​ tables, let's first select​ into smart​ The added​ cases are clearly visible, we will apply​ the differences.​ also visually format​ the solutions in this​​ elements

​using formulas =COUNTIF(List1,D5)​

​can be used with​​ like this

​ days in periods​ on the button of the​ “Equals” function, and then​ - or the inscription​https://otvet.mail.ru/question/87175757​ tables. Doesn't require columns

How to compare two tables in Excel using the VLOOKUP function

In this option, we will use the VLOOKUP function, which will allow us to compare two tables for matches. To compare two bars, enter the formula =VLOOKUP(C2,$D$2:$D$7,1,0) and copy it to the entire range being compared. This formula sequentially begins to check whether there are repetitions of the value from column A in column B, and accordingly returns the value of the element if it was found there; if the value is not found, we get the #N/A error.

How to compare two tables in Excel with the IF function

This option involves using the logical IF function, and the difference between this method is that to compare two columns, not the entire array will be used, but only that part of it that is needed for comparison.

For example, let’s compare two columns A and B on the worksheet, in the adjacent column C we enter the formula: =IF(ISERROR(SEARCH(C2,$E$2:$E$7,0));"";C2) and copy it to the entire calculated range. This formula allows you to view sequentially whether certain elements from the specified column A are in column B and returns the value if it was found in column B.

Finding differences in two lists

29361 06.11.2012

A typical task that every Excel user faces periodically is to compare two ranges of data and find the differences between them. The solution method, in this case, is determined by the type of source data.

Option 1: Synchronous lists

If the lists are synchronized (sorted), then everything is done quite easily, because you essentially need to compare the values ​​in adjacent cells of each row. As the simplest option, we use a formula to compare values, which produces the output logical values ​​TRUE

or
FALSE
:

The number of mismatches can be calculated by the formula:

=SUMPRODUCT(—(A2:A20<>B2:B20))

or in English =SUMPRODUCT(—(A2:A20<>B2:B20))

If the result is zero, the lists are identical. Otherwise, there are differences in them. The formula must be entered as an array formula, i.e. After entering a formula into a cell, press Enter

, and on
Ctrl+Shift+Enter
.

If you need to do something with different cells, then another quick method will do: select both columns and press the F5 , then in the window that opens, the Select button (Special)

—
Row differences
.
In the latest versions of Excel 2007/2010, you can also use the Find & Select - Go to Special on the Home tab.

Excel will highlight cells that differ in content (by row). They can then be processed, for example:

• fill with color or otherwise visually format
• clear with Delete
• fill everything at once with the same value by entering it and pressing Ctrl+Enter
• delete all rows with selected cells using the command Home - Delete - Delete Rows
• etc.

Option 2. Shuffled lists

If the lists are of different sizes and are not sorted (the elements are in different orders), then you will have to go a different way.

The simplest and fastest solution: enable color highlighting of differences using conditional formatting. Select both ranges with data and select on the Home tab - Conditional formatting - Cell selection rules - Duplicate Values :

If you select the Recurring

, then Excel will highlight matches in our lists if the
Unique
- differences option is selected.

Color highlighting, however, is not always convenient, especially for large tables. Also, if elements can be repeated within the lists themselves, then this method will not work.

As an alternative, you can use the
COUNTIF
function from
the Statistical
, which counts how many times each element from the second list appeared in the first:

The resulting zero indicates differences.

And finally, “aerobatics” - you can display the differences in a separate list. To do this you will have to use an array formula:

Looks scary, but does the job perfectly

Related links

• Highlighting duplicates in a list with color
• Comparing two ranges using the PLEX Add-in
• Prohibiting the entry of duplicate values



Albert

06.11.2012 23:17:08

Method 5. Checking the occurrence of elements of one list in another Thank you very much for the method!!!! Ato sat there all day and couldn’t do anything. No matter what I installed, it turns out to be so simple))). Link

Svetlana

06.11.2012 23:17:55

Help me please. I use method 5. There are 500 text values ​​in one column. The other column contains the text processed by the “drop-down list” macro. I compare these two suns (COUNTIF). They contain obviously identical cells with text, but the formula gives me 0 (does not find). Moreover, if I manually reset the value of any cell in the first column, it immediately enters 1 (finds). The formats of the cells in the columns seem to be the same, but I don’t understand what’s wrong? Link

Nikolay Pavlov

06.11.2012 23:20:37

The problem may be in extra invisible spaces or in the Latin alphabet - the English “C” from the Russian “C” cannot be distinguished externally, but for Excel these are already different meanings. Parent Link

Igor

06.11.2012 23:19:30

And I went ahead and wrote my own utility for comparison. Because usually the comparison algorithms are either not described or are poor. You just have to copy data from Excel into text files, but oh well. But you can process simply huge lists by Excel standards. I spent 2 weeks working on this matter... please don't blame the administration for the link. https://fobosworld.ru/programs/raznoe/68-compare Link

Nikolay Pavlov

06.11.2012 23:21:01

No one will kick, quite the opposite. Thanks for sharing. Parent Link

Olga

06.11.2012 23:20:02

Thanks a lot to the author! Everything is very clear. Link

davaispoem

23.04.2013 03:50:17

Please tell me, is it possible to add a third list in which the first two lists will be compared, with the first list taken as a basis, and from the second one selected those values ​​that are not in the first and added to the third list? Well, it’s very necessary... Link

Nikolay Pavlov

10.05.2013 22:49:13

You can simply combine two data ranges into one and then remove duplicates (Data - Remove Duplicates). Parent Link

Yuri Smirnov

30.05.2013 13:16:06

Good afternoon. I have a question. There is data in three columns. It is necessary to compare them line by line and highlight the cell with the lowest value in color. When you use the formula (=$A1=MIN($A1:$C1)) in conditional formatting for the entire range, the entire row is highlighted. This formula works, but only if you select the columns separately and write this rule to each. The actual question is: is it possible to describe this operation with one condition when selecting the entire range (A1:C10 - conditionally) at once? Thank you in advance. Link

Nikolay Pavlov

30.05.2013 14:00:04

Yuri, just remove the dollar before the first A1, i.e. need =A1=MIN($A1:$C1) Parent Link

Yuri Smirnov

30.05.2013 14:05:38

Thank you, I didn’t even notice the elephant. Parent Link

Yuri Smirnov

30.05.2013 17:13:47

Is the formula for counting cells that satisfy this condition correct in my case (that is, counting in a column the number of cells that have minimum values ​​in the row)? Something isn't working. =COUNTIF(B7:B97,B7=MIN($B7:$J7)) I count the number of minimum prices from several suppliers. Thank you. Parent Link

Nikolay Pavlov

31.05.2013 16:28:22

The condition of the COUNTIF function must be a text string. Unfortunately, you cannot insert logical expressions there. Count the minimum in a single cell and insert the address of that cell as the second argument to the COUNTIF function. Parent Link

Yuri Smirnov

31.05.2013 16:34:19

Thanks for the advice, I'll try it. Parent Link

Clara Z

16.07.2013 20:20:55

When I do it using method 1, for some reason it displays “TRUE” throughout the entire column, even where there should be “FALSE”. I click on the wrong cell “TRUE” - the value changes to the correct one - “FALSE”. How to make it so that it is immediately clear where the value is “TRUE” and where “FALSE”? Link

Nikolay Pavlov

21.07.2013 12:02:18

You probably have manual recalculation of formulas enabled. Look on the Formulas - Calculations

.
Set to Automatic
. Parent Link

Irina

22.07.2013 09:35:18

Please tell me. It is necessary to compare two ranges A1:C20 and E1:G20. In the range E1:G20, select those cells that do not match the range A1:C20 using conditional formatting. When writing the formula =E1=A1 everything is OK, but I can’t figure out which formula to set for the entire range??? Link

Nikolay Pavlov

22.07.2013 20:27:01

Select the first range, create a conditional formatting rule with a formula and enter =COUNTIF($E$1:$G$20,A1)=0 Parent Link

Irina

24.10.2013 17:32:47

Please tell me, I need to compare two text ranges A1:A20 and B1:B20. Text values ​​are sorted in different orders; it is necessary that in column C1:C20 those values ​​are indicated that are not in column A1:A20, but are in column B1:B20. Formula if(eerror(search(B1;A$1:A&20;0));"";B1)

displays only matching values, but vice versa is needed. Please help. what's wrong. Link

Nikolay Pavlov

02.12.2013 20:39:12

Why is Method 5 not suitable? Parent Link

abdul abdulov

18.12.2018 12:13:19

where is this method 5 that you are all talking about: cry: Parent Link

LOLO ALL

27.11.2013 11:13:40

Good afternoon, I am comparing ranges using the 5th method. The results are disappointing - 1 - where it is necessary and not necessary, and the same with 0(((( I compare B2:B1683 with A2:A1530. The formula looks like this: =COUNTIF(B2:B3211,A2:A4370). At the same time it gives an error “the formula does not cover the adjacent cell.” Also, dragging the formula over the entire range is carried out with automatic shifting of the cells in the formula by 1 step, namely =COUNTIF(B3:B3212;A3:A4371) and each subsequent one is also shifted, which makes it difficult using this method for large ranges. Please tell me where my mistake is. Thank you. Link

Nikolay Pavlov

02.12.2013 20:37:59

The second argument of the COUNTIF function is one cell with the criterion, not a range. Please take a closer look at the example file. Parent Link

Elena

09.01.2014 12:57:32

Tell me: there are two lists, you need to find out which elements are in the first list, but not in the second (disappeared), and also which are in the second, but are missing in the first (added during the period). ideally, in a separate column so that the lost elements are selected, and the added ones in another... Link

Nikolay Pavlov

09.01.2014 13:27:26

Elena, what about Method 5

not suitable for this? And then filter 1 or 0 to see only new or old elements. And if you want everything to happen on its own and the difference to be uploaded to a separate list, then look at the range comparison function from my PLEX add-on. Parent Link

Oleg Gelor

15.05.2014 03:06:19

Please tell me how to solve this problem. Three columns A, B and C. Column “A” contains a list of words and expressions. Column "B" contains a larger list of words and expressions than column "A". You need to enter a formula in column “C” that will compare the values ​​of the cell in column “B” with all the cells in column “A”. If the values ​​in the cells of columns “A” and “B” coincide, the “*” sign appears in the cell on the right (column “C”); if there is a mismatch, the cell on the right remains empty. One more thing: It is necessary for the formula to work even if the cell texts do not exactly match in columns “A” and “B”, because words can have different endings, different case, and the word order can be different. A formula like this works well for this task: =IF(ISERROR(SEARCH(A$1,B17)) ;"";"*") But it compares the cells of column “B” with only one cell of column “A”, but I don’t know how to make it check all the cells of column “A”, but solving this issue would solve the whole task. Please tell me how this can be accomplished. Thank you! Link

Maksim

18.06.2014 11:36:35

How to check text values ​​of two different columns with approximate values? Link

Anna

13.04.2015 10:06:50

Good afternoon Help me please! There is a table with item codes and weeks, you need to understand how many changes there were in the period. Can this be done using formulas? Thank you! Link

Rua

19.05.2015 16:05:51

Hello! Please help me solve this problem: I need to compare the values ​​(full name) and assign unique identifiers (1, 2, 3, etc.) to unique full names in a separate column (that is, the same full names must have the same identifiers). Link

Dmitry Romanov

23.06.2015 11:26:38

Hello! There are three columns, these are “article”, “half-year” and “quantity”, you need to compare them with another table, where there is also “article”, “half-year” and “quantity”. Articles can be repeated, it turns out that you need to take the amount of the “article” of a certain “half-year” and compare it with the amount of the “article” of the same “half-year” but from a different table. the final discrepancy is important (i.e. + or - in quantity) Please tell me how this can be implemented, thank you. Link

Nikolay Pavlov

07.02.2016 15:10:24

Dmitry, you first need to summarize the data by article (with a pivot table or the SUMIF

), and then compare using any suitable method from this article. Parent Link

Ilshat

05.02.2016 14:48:22

Good afternoon, Nikolay! Thank you very much for the array formula, a really beautiful solution). I watched your video several times, tried to implement this method, until the last moment of substituting the formula from the extra column, everything worked and the result was correct. As soon as I make a substitution and enter an array formula, the result is distorted; in my case, out of 7 differences, only 5 remain. I just can’t understand what’s wrong... The office costs 2020, as far as I understand, the same as yours. Could something else be the problem? Thanks in advance Link

Nikolay Pavlov

07.02.2016 15:09:20

Ilshat, maybe they haven’t forgotten about fixing absolute links with dollars? Download the attached file with an example and see how it’s done. Parent Link

Ilshat

08.02.2016 09:19:08

Thank you, Nikolai, I didn’t forget. Initially, I downloaded your example and copied the array formula, substituting my ranges, but it didn’t work out correctly. Then I decided to do everything step by step from the video. I haven't found the error yet) Parent Link

white-hot

15.03.2016 22:50:10

Nikolay good evening! Tell me please! I’m comparing two lists, price, assortment using option 2, I highlighted the unique ones, edited the desired list, I need to copy it. But it’s pasted in conditional formatting in color, but how can I cancel all this so that it’s copied normally? Thank you! Link

JURA TAFIJ

19.03.2016 21:01:44

Good afternoon. I have this situation... There is a folder with goods for the year. This folder contains 12 product folders by month. Each folder contains 20-22 Excel books named by date. These books have from 1 to 15 sheets and each has product codes. When a new product arrives, I need to check whether we have had this product before. And at the moment I need to check the goods for the whole year, whether we had the same goods twice (for example, product codes from April, do they not match the product codes for July). Each book is called a date, each sheet is a supplier. Is it possible to create a pivot table? from such a large number. books and then filter out duplicates? Or are there other ways to verify data? Thank you very much for every advice! YURA. Link

Igor Gromov

06.04.2016 18:53:36

with identity everything is clear. But if you need to compare when in one column there is data both identical and different. then how to identify them? Link

Konstantin Silich

18.07.2016 14:05:57

Good afternoon, Nikolay! I find your site very useful and informative; it has helped me more than once in solving difficult problems. But now I'm faced with a problem for which I couldn't find a solution. The essence of the problem is that once I created a table and applied an advanced filter to it, as you described in the topic “Advanced filter and a little magic”, as a result of a search on this table, the list “A” is obtained. The question is, is it possible to compare this filtered list “A” with three more lists “B”, “C” and “D”, so that the name of the list in which the maximum number of matches with the list “A” appears in a separate cell? Thank you in advance! Link

Nail Gafiatullin

23.08.2016 13:58:29

Hello! Convenient thanks. But I'm used to comparing using VBA .. this is probably because I'm a programmer at heart Comparing two lists using VBA Link

bogdan yakimov

25.08.2016 08:44:09

Good afternoon. Please help me with this task. There are two columns of values ​​that need to be compared to find the remaining rows. They differ in the number of lines and the form of recording in the cells. But the bottom line is that column "1" contains fewer values ​​than column "2". However, in “2”, up to six values ​​can be written on one line, which in column “1” are written separately in each line. It would be great if an example was attached. Rows -6000. Link

Zedd Zorander

08.12.2016 18:18:52

Nikolay, Hello! Help me please. I’m using your last method, but for some reason not all unique values ​​from the 2nd column are displayed =IFERROR(INDEX($C$1:$C$21,SMALL(IF(COUNTIF($A$1:$A$21,$C$1 :$C$21)=0;ROW($C$1:$C$21));G9)-1);"" A little confusing for column G. If there is a 1 in column G, then the first unique word from column C outputs, and then if you put the number 2 in column G, it displays the word that is in column A. I don’t understand anything. Thank you! Link

Maxim L

23.12.2016 14:33:39

Good afternoon Please tell me: 1. There are 2 tables: Table No. 1 has: 1 column with data and 3 subsequent columns are empty. Table No. 2 has: 4 columns with data. 2. The following task is required: It is necessary to compare column No. 1 of both tables for the presence of duplicate values; if the values ​​are repeated, then the data in columns No. 2, 3, 4 of table No. 2 must be written into the corresponding empty columns of table No. 1. Thank you very much in advance. Link

York

27.01.2017 11:44:49

Good afternoon, ladies and gentlemen! There are problems using method 5 (aka method 6 in the example file), I just can’t figure it out myself. I hope the collective wisdom can help! Comparison range: list 1 - 192 items, list 2 - 191 items. When changing the array of ranges A2:A10 and C2:C10 in the formula (the formula correctly produces three items from list 2, different from list 1) to A2:A193 and C2:C192 (the formula produces only 1 item)… and when changing the range, { } brackets, when you try to add brackets {} again, only the formula itself is displayed... and the “Show formulas” button is not pressed... MS standard 2010 link to excel here Link

Tatiana Boronina

27.02.2017 17:32:26

Guys, can you help me? There is a list with complete address data (original address - column A) in one column and broken down into components (City. Street) - in the following columns. It is necessary to highlight the matches with color - in the most beautiful scenario, I would like to highlight the cell with the matching City (column C) and change the font color if the Street matches (column B). For example, Zelenograd is not listed in line 7, which means it will not be highlighted; Aviatornaya Street is not listed in line 19, which means it will also be highlighted. It is required to find the maximum matches of columns B and C with column A File attached Book 3 Link

Anrey S

26.06.2017 15:45:32

Link

qew wed

12.02.2018 11:39:04

in G1 formula and copy or drag down =IFERROR(VLOOKUP(A1,$D$1:$E$10,2)-B1;"") Parent Link

Sergey Rudometov

11.08.2017 13:32:54

Good afternoon, Nikolay. Please tell me in your formula: =SUMPRODUCT(—(A2:A20<>B2:B20)) what role they play, what the symbols are for and what they mean - Link

qew wed

12.02.2018 10:59:00

to avoid column G, you can change the formula instead of G2 - ROW(A1) {=IFERROR(INDEX($C$1:$C$21,SMALL(IF(COUNTIF($A$1:$A$21,$C$1:$C$21) <>0;ROW($C$1:$C$21));ROW(A1)));"")} array formula compares the first to 21 rows and outputs the same ones Link

Sam Phelps

15.02.2018 05:40:04

Good day Please help me solve the problem: given only one column of 1000 rows with the letters: A, B, C, D, E, F, G, H in a chaotic order, how to calculate the number of occurrences of such situations, where B follows A, E follows A, C follows A, and so on for each Unique letter. There is a slightly simpler option, a column of many rows with numbers from 0 to 3. Also, how to calculate the number of occurrences of such situations, where 1 follows 0, 1 follows 2, 1 follows 3, etc. Please, help, who knows any solution! Link

Yuri Berestnev

16.03.2018 18:36:43

Good evening! Tell me how to type a formula to create conditional formatting (filling with color) on the principle that if the value of any individual formatted cell (for example A2) is equal to a value from the range C2:C70 and with this match in the matching row, the cell of the adjacent column is in the range F2:F70, will correspond to the value of Z2. The condition primitive works =AND(A2=C2;F2=$Z$2), it must be translated into a universal formula, taking into account the simultaneous coincidence of a cell with a cell from the first range and in the line of coincidence of a cell from another range with a specific cell or value (For example, “TO” ;) It’s very necessary - help, fellow forum users! Link

Alexey Popov

04.04.2018 08:26:17

Good evening. Tell me how to do it. You need to compare column A (A5 to A50) with three columns D5-D50, E5-E50, F5-F50 row by row. If the number in column A is less than the number in column D, then add one in cell A1. If it is more than in column D, but less than in column E, then add one in cell B1. If more than in column E, but less than in column F, then add one in cell C1. If it is greater than in column F, then add one in cell D1. The numbers in column D are greater than those in column E, and the numbers in column E are greater than the numbers in column F row by row. Link

ruslan kitenko

21.06.2018 12:22:09

Good afternoon, please tell me the solution. Option 1 no longer works on three lists (=A1=B1=C1). What if there are twenty lists? Link

ruslan kitenko

21.06.2018 15:00:09

I decided only through =IF(AND(….)). Link

Alexey Zhuravlev

26.02.2019 21:44:51

Good evening! There are two columns with monetary amounts, with pennies, which totals approximately 10,000 rows! These amounts can be repeated in both columns in different rows several times and you need to fill in all matches! Matches are allowed with a difference of up to one ruble! For example, in A1 = 1052.78 and in B200 = 1053.76, then this should be considered a coincidence and these cells A1 and B200 should be filled with yellow, and so all 10,000 rows should be compared, and unmatched amounts should remain unfilled ! PEOPLE, PLEASE HELP, SOME WITH A MACRO, SOME FORMULA, SOMEONE WITH ANYTHING YOU CAN! I have two days left for this, and then execution Link

Shahin Karakhanov

17.04.2019 16:06:10

Is it possible to create a formula with an array in a macro? Otherwise, this method for long tables makes it difficult to work with. Link