?
Solved

excel column find duplicate count and compare value

Posted on 2016-08-09
28
Medium Priority
?
101 Views
Last Modified: 2016-08-11
I need a formula, please see sample screenshot, my raw data contain in single column

Image-716.png
0
Comment
Question by:Julio Jose
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 9
  • 5
  • +2
28 Comments
 
LVL 52

Expert Comment

by:Rgonzo1971
ID: 41748753
Hi,

pls try

=Countif(A:A,B2)

Open in new window


and

=if(Countif(D:D;B2),TRUE,FALSE)

Open in new window


Regards
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41748755
Please refer to the attached for all the formulas.
Julio.xlsx
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41748756
If  column A is RAW and Row 1 is RAW
For the count, in C2 type =COUNTIF($A$2:$A$12;B2) - if you are in the US type in
=COUNTIF($A$2:$A$12,B2)
and copy down the formula to C3

and for the compare type in E2 and copy down the following
=IF(ISERROR(VLOOKUP(D2;$A$2:$A$12;1;FALSE));FALSE;TRUE)
or in the US
=IF(ISERROR(VLOOKUP(D2,$A$2:$A$12,1,FALSE)),FALSE,TRUE)

See my sample file attached
example.xlsx
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 33

Expert Comment

by:Rob Henson
ID: 41748789
You can also achieve the same with Pivot Table functions. Also by converting the list to a table, the Pivot range will expand as the data expands.

The List of unique entries and the count of each is a Pivot Table. The compare columns then use the GETPIVOTDATA function within an ISERROR function. If the criterion specified for GETPIVOTDATA don't exist in the Pivot, it will give an error. By reversing that with a NOT function, you get the desired results.

See attached.

Thanks
Rob H
Pivot.xlsx
0
 

Author Comment

by:Julio Jose
ID: 41748799
Can column B get the unique value in the column A use formula
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41748830
You can use an INDEX/MATCH combination
In the attached example I have named the RAW list of fruit (A2:A12) and used Ctrl+Shift+Enter to enter the formula in B2, and copy B2 down to B3:B12.
example_v2.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41748846
In the file I uploaded, just delete column E and then delete column B.

It then has the format/layout the same as your screenshot.

Thanks
Rob
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41748859
Can column B get the unique value in the column A use formula
The file I uploaded in Post ID: 41748755, the column B already contains a formula to show the unique values from column A. :)
0
 

Author Comment

by:Julio Jose
ID: 41749695
The column A actually contain 459 row data, I tested the formula not work
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41749820
You will need to adjust the ranges in the formula as the sample workbook I uploaded assumed that you have only 12 rows of data.

If you have 459 rows of data, you may try the formulas like this...

To get unique entries in col. B.
Try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone. i.e. copy the formula given below --> delete the existing formula from B2 --> press F2 (function key) to go in edit mode --> paste the copied formula but don't hit Enter now --. hold down the Ctrl + Shift keys together and then hit Enter.

In B2
=IFERROR(INDEX($A$2:$A$459,MATCH(0,INDEX(IF($A$2:$A$459<>"",COUNTIF($B$1:B1,$A$2:$A$459)),0,0),0)),"")

Open in new window

and then copy the formula down until you get blank cells.

To count for occurrences of each unique entry in col. B.
Try this.....
In C2
=COUNTIF($A$2:$A$459,B2)

Open in new window

and then copy it down.

In E2
=ISNUMBER(MATCH(D2,$A:$A,0))

Open in new window

and then copy down.

If that doesn't work for you, please upload a sample workbook.
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41749859
Setting the list as table and then usingva pivot table, it doesn't matter how many rows of data; just refresh the pivot and it will include the new data.
0
 

Author Comment

by:Julio Jose
ID: 41749901
Subodh, sample data attached
0
 
LVL 32

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41749910
I think you forgot to upload it. :)
0
 

Author Comment

by:Julio Jose
ID: 41749976
0
 
LVL 32

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41749982
Please refer to the attached.
Julio1.xlsx
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41749986
check out your file with example of unique items
named the data in column A as a named ranged - RAW and used that in your formulas for easier reference
Julio1_wUniques.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41749996
Updated version using a pivot and GETPIVOTDATA function.
Pivot.xlsx
0
 

Author Comment

by:Julio Jose
ID: 41750008
when I replace the column A with my actual data, the column B does't show the accurate information
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41750009
To who was that comment addressed - which solution, formula or pivot table???
0
 

Author Comment

by:Julio Jose
ID: 41750012
Rob, I tried both the same
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41750020
With the new data in column A, select a cell in column B and right click, choose Refresh from the pop up menu.

Or when in the pivot table there will be extra tabs on the ribbon for Pivot Table Tools. The tab for Options will have a Refresh button.

If you are just pasting your existing data on top of what is already there, the extent of the Table may not be expanding correctly. Before pasting, choose a cell in column A and you will have an additional ribbon group for Table Tools with one tab Design. On this tab over to the left there is an option for "Convert to Range". Click this and then paste in your new real data.

Then select all of the data in column A and press Ctrl + T to start the create Table wizard. The range should already be populated but ensure you check the box for "My data has headers" and then OK.

This will however rename the Table in sequence, Table1, Tabl2 etc. The Table Design tab will have re-appeared, in the top left there is an input box to rename the Table, rename it as Table1 and the Pivot should refresh correctly.

You will not have to do this everytime, as you add new data to the bottom of the list it will expand the table and the Pivot Table will refresh properly.

Thanks
Rob H
0
 

Author Comment

by:Julio Jose
ID: 41750024
I'm lost in "Convert to Range", using Excel 2007
0
 
LVL 33

Accepted Solution

by:
Rob Henson earned 1000 total points
ID: 41750025
OK, I am using Excel 2010; I don't recall where that option was in 2007.

Another option attached.

Similar to suggested by xtermie with a Named Range, but the named range is Dynamic so that it changes as the data expands.

Pivot Table on second sheet is based on data in Named Range rather than Table1.
Pivot.xlsx
0
 

Author Comment

by:Julio Jose
ID: 41750033
don't understand what is the problem, paste 10 then refresh I got a accurate data but paste 100 is getting wrong again.
0
 
LVL 18

Assisted Solution

by:xtermie
xtermie earned 500 total points
ID: 41750035
In my case can you also update the named range RAW to be the new data range in A?
Also could you please sort column A?

Check attachment
Julio1_wUniques_vo.xlsx
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41750036
Are you pasting into Dynamic sheet or Pivot sheet?

If pasting into Dynamic sheet, there should be no problem; whereas Pivot sheet will have the issue of resetting the Table1 as described above.

Did you find the Table conversion settings for 2007? At some point it used to be referred to as a List but I thought that was 2003 and prior.

Thanks
Rob
0
 
LVL 33

Expert Comment

by:Rob Henson
ID: 41750107
Just Googled converting table to range in 2007 and found this on theexceladdict.com

Convert Excel 2007 Table To A Normal Range:
1) Right-click any cell within the table;
2) On the pop-up menu point to Table, then Convert to Range. You will be asked to confirm your action with a message “Do you want to convert the table to a normal range?“

Thanks
Rob
0
 

Author Closing Comment

by:Julio Jose
ID: 41752356
thank you all
0

Featured Post

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!

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

752 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