Create helper column that marks distinct values

[hypetaxx]

hi,

I have a table with 200.000 rows.

I need to create a helper column, that marks distinct values with 1 and not distinct "" or 0.

I tried the following but both use countif and I believe it is the problem it's just to slow.

=IF(COUNTIF($A$2:A2;A2);1;0)
=1/COUNTIF($A$2:A2;A2)

but both are really slow +20 mins to calculate and many times Excel crashes( 2007 32bit).

The workbook is really simple

Col1 (number formatted as text) - Col2 =distinct


Can anyone help me out I googled, searched the forums, checked out youtube, but I can't seem to find a way that works.
If it is better to do this by VBA and recalculating by a macro then that it is a fine solution as long as it works and doesn't take forever to finish!.

Thanks in advance

BR
Thomas

 

[circledchicken]

Hi and welcome to the forum,

An easy (and fast) way to get the distinct values is using a PivotTables:

• Select all your data (including the column header)
• Go to the Insert Tab, select PivotTable and click OK (or keyboard shortcut Alt > D > P and then Finish)
• Drag the field name into the Row Labels section

Here are a few useful resources for learning more about PivotTables:

• Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
• Overview of PivotTable and PivotChart reports - Excel - Office.com
• Excel Pivot Table -- Dynamic Data Source

 

[saelageonzon]

You can use this formula instead:
=IF(MATCH(A2,$A$2:$A$200000,0)=ROW()-1,1,0)

This will tag the first occurrence of your data.

My suggestion is to use match and row functions instead. Lookup functions always return the first occurrence when the lookup value is found multiple times on a range.
Match function returns the position number the lookup value is located while row() function returns the current row the formula is entered.

Countif function is a heavy function since it will be comparing one criteria with a range, which for a 200,000 row data, will eventually crash Excel. It is not advisable to use this for heavy data.

 

[hypetaxx]

Hi and welcome to the forum,

An easy (and fast) way to get the distinct values is using a PivotTables:

• Select all your data (including the column header)
• Go to the Insert Tab, select PivotTable and click OK (or keyboard shortcut Alt > D > P and then Finish)
• Drag the field name into the Row Labels section

Here are a few useful resources for learning more about PivotTables:

• Office 2010 Class #36: Excel PivotTables Pivot Tables 15 examples (Data Analysis) - YouTube
• Overview of PivotTable and PivotChart reports - Excel - Office.com
• Excel Pivot Table -- Dynamic Data Source

I should have mentioned it, but the helper column is to be used in a pivotable where I need a count of unique ID's.

 

[circledchicken]

I should have mentioned it, but the helper column is to be used in a pivotable where I need a count of unique ID's.

Ok, then there are a number of fast alternatives you can consider including:

• PowerPivot (for Excel 2010+)
• Using SQL within Excel
• A VBA solution using Charles Williams' COUNTU UDF
• The COUNTDIFF function from the MoreFunc add-in

PowerPivot is the easiest in my opinion - see here for how to use it:

• Unique Count in Excel Pivot Table With PowerPivot

For more on the other options see for example:

• http://www.mrexcel.com/forum/excel-questions/663393-countif-unique-values-only-2.html#post3293812
• http://www.mrexcel.com/forum/excel-questions/579045-count-unique-list-criteria.html#post2864652

 

[hypetaxx]

I have the morefunc add-in using it with much pleasure.

I took a look at the COUNTDIFF can't seem to get it to do what I want, can you assist with how I would build the formula?

Thanks advance

 

[circledchicken]

I have the morefunc add-in using it with much pleasure.

I took a look at the COUNTDIFF can't seem to get it to do what I want, can you assist with how I would build the formula?

Thanks advance

To get a count of the unique ID's you should just be able to do something like:
=COUNTDIFF(A2:A200000)

(And use the optional arguments of the function if you want - i.e. to ignore blanks or exclude certain values).