# Excel 2007 :: Count Number Of Duplicate Rows With Two Columns?

Jun 3, 2012

I want to count the number of duplicate rows where the exact text in columns A and B match. An example is as follows, where column C would be the desired result. Note that there are hundreds of different text values of column A and hundreds of column B, I just simplified the example.

Excel 2007
ABC1AX72AX
3AX
4AX
5AX
6AX

[Code] ......

## Excel 2007 :: Macro For Finding Out Missing Number And Duplicate Number In A Range

Jan 22, 2013

Excel 2007
ABCDE12145101843222121028543291410388563015104796731501058178325210
685894953107839104354108841011445510985111349661101215516710013135668
981417576999151858708916195971801720607291182161738219236274902024637
59121276476872228657786232425Sheet1

## Excel 2007 :: Combining Data In Duplicate Rows

Aug 15, 2008

I have a long (about 180 000 rows) list of part numbers, descriptions, prices and quantities. There are many duplicate part numbers. I need to go through and find the duplicates, add all their quantities into one cell and delete the remaining duplicates. I have used conditional formatting to find the duplicates easier but cannot use the remove duplicates button as it will delete the whole row and I need the quantity information in each row.

Any formula or macro I can set up to search the part number column (F) for duplicates and add the figures in the quantity column (J) and THEN delete the duplicate part number rows?

Maybe I need to put the new combined information in another sheet?

## Excel 2007 :: How To Duplicate Rows Based On Cell Value

Feb 26, 2014

What im needing is something that will duplicate each row based on Column F as shown below... However I also need the duplicate row numbers added to Column G and then the original row deleted... I will try and show you an example of the input/output i would like to achieve: (also the first row can be ignored it will all be headers)

INPUT

ROUTE
NAME1
NAME2
DESC
3
WOOD000001
PW
ID

[code].....

## Excel 2007 :: Macro - Move Duplicate Rows From One Worksheet To Another

Jul 4, 2014

I am using Excel 2007 on Windows 7 Home Premium 64bit.

My Workbook contains 2 Worksheets, both with the same headings in row 1. Sheet2 is initially empty, except for the headings in row 1.

The following is a sample of the data in Sheet1:

[Code] ......

I want to create a Macro to operate as follows:

In the above sample data, rows 2, 4, and 8 are identical, so I want to copy rows 4 and 8 to Sheet2, and delete them from Sheet1.

Similarly:

Rows 3 and 9 are identical, so row 9 should move to Sheet2;
Rows 5 and 11 are identical, so row 11 should move to Sheet2.
Rows 6, 7, 10, and 12 are all unique, so should be unaffected by the Macro.

New records will be added to Sheet1 periodically, so the Macro will be used each time to move any newly discovered duplicates to Sheet2.

The Macro should make no assumption about how the rows are ordered, and should not change their order.

I have uploaded two Workbooks, such that Workbook1 shows the original data, and Workbook2 shows the result I want to obtain.

Attached Files :
Workbook1.xlsm‎
Workbook2.xlsm‎

## Excel 2007 :: Count Number Of Cells In A Range That Have Text

Feb 7, 2012

I'm using excel 2007, here's what I need.

I want to count the number of cells in a range that have text in them (any text at all) but not count them if they have numbers in them or are blank. How would this formula be written?

## Excel 2007 :: Count Number Of Digits Without Counting Digit Twice

Sep 10, 2013

The values in columns b:d range from 000-999. I need a formula that will count the number of digits in B:D that match the 3 values in H1 without counting a digit twice. The existing formula counts a digit twice, like the value in H4.

DIGIT HITSABCDEFGH10,1,2209/09/132192191222309/09/134824821441409/08/131191191112509/08/139799792550609/07/137177171551709/07/138668662000
Excel 2007Worksheet FormulasCellFormulaE2=B2&C2&D2F2=SUM(B2:D2)G2=MOD(F2,10)H2=SUMPRODUCT(--ISNUMBER(FIND(","&\$B2:\$D2&",",","&H\$1&",")))E3=B3&C3&D3F3=SUM(B3:D3)G3=MOD(F3,10)H3=SUMPRODUCT(--ISNUMBER(FIND(","&\$B3:\$D3&",",","&H\$1&",")))E4=B4&

[Code] ......

## Excel 2007 :: Pivot Table - Max Number Of Columns?

Nov 9, 2011

I'm using Excel 2007. My pivot table seems to be limiting me to 256 columns in the Values/Data area. In researching below I believe that I should be able to have 16,000 columns in my Pivot Table.

[URL] The "Big Grid" and Increased Limits in Excel 2007

PivotTables Maximum rows displayed in a PivotTable report is 1 million.
Maximum columns displayed in a PivotTable report is 16,000.
Maximum number of unique items within a single Pivot field is 1 million.
Maximum number of fields visible in the Fields list is 16,000.

## Excel 2007 :: Disable Insert Columns / Rows Only

Mar 1, 2012

I am looking for a way of allowing users to edit the existing cells at their will, but simultaneously disabling the option to insert/ delete columns or rows.

By enabling Sheet Protection, I cannot find a way to do it. Can I?

I am on MS Excel 2007 by the way.

I found the way, just unlock all cells in Format Cells, Protection Tab. Then Protect sheet and de-select what you want to disable.

## Excel 2007 :: ID Number Formatting Lost After Text To Columns Is Used

Jul 16, 2014

I have a text file that I need to open in MS Excel 2007. The file contains the following data. (Each column is delimited by the "|" character.)

Part #|Inventory ID
1743|213,221
1864|10,40
1948|1170,1180
5265|100,104,107,10004

Ultimately, I need three columns but the data needs to look the same as it did before I opened the file.

When I open the file for the first time, I use “Text to Columns” to delimit the fields by the "|" character. The problem is that the Inventory IDs in the middle column get a bit whacky.

Here's what I'm left with:
Part # Inventory ID
1743 213,221
1864 10,40
1948 11,701,180
5265 10,010,410,710,004

The comma is retained appropriately when there are two 2- or 3-digit Inventory IDs.

When a row has multiple, longer Inventory IDs or Inventory IDs that vary in length, the comma is moved to every 3rd decimal place.

I tried putting quotation marks around the data before I opened the text file for the first time but that didn’t do anything. I tried Custom Formatting to retain the comma position (0000,0000) but this only worked when I had two 4 digit IDs. I also changed the number type to Text and General. Once the comma was changed, I couldn’t figure out how to get it back.

How to best retain the format of my Inventory IDs.

## Excel 2010 :: VBA Code To Search Multiple Columns And Delete Any Duplicate Cells (not Rows)

Jun 12, 2014

My Excel program (Excel 2010) currently has several columns and each column looks for and pulls data from a specific file on my computer. Then I need to delete any duplicate data entries, count the number of unique entries and track the changes through a chart. I have everything done except I cannot figure out (or find on the internet) a way to search in multiple columns (more than 2) and delete just the duplicate cells. I want to delete the cells in a way where there is one left. For example if the code 12gf is duplicated three time, I want to be left with one 12gf (it doesnt matter what column the original one is left in). Additionally, column length changes and they are not sorted. I have attempted to attach an image of an example file below.

## Count Columns With Data Then Insert That Number Of Rows

Feb 11, 2014

Original

01012014 DAVE JOHN
02012014 MAT
03012014 CHRIS MIKE PHIL

What I want

01012014 DAVE
01012014 JOHN
02012014 MAT
03012014 CHRIS
03012014 MIKE
03012014 PHIL

## Count Number Of Rows With Unique Entries In 2 Columns

Oct 9, 2006

I have a spreadsheet which is to record quality checks on work carried out by staff. The spreadsheet has a customer reference number in column B and a Staff reference number in column C.

I can carry out a number of checks on a member of staff on one transaction, so for instance, I could carry 3 checks on one customer number, which would result in the staff ref number being enetered 3 times (there is 1 check per row).

I need a formula to count the number of checks I carry out on each member of staff. My problem is that although 3 checks could be completed on someone, if it is on the same customer NO, it only counts as 1 check. In effect, I need a formula to count the number of staff ref numbers which have a unique customer number eneterd in the adjacent column.

All the cust numbers are unique so would I be able to use a wildcard?

## Excel 2007 :: Subtotals With HIDDEN Rows AND Columns Given Some Criteria

Aug 8, 2014

I am trying to develop a compliance report with Excel 2007) based upon a simple pass/fail criteria. The subtotals must be tracked both by Device (column) and by Requirement (row). There are macros (not included in the attached sample) that hides both columns and rows. I successfully found an example which I modified to correctly calculates data for a column when rows are hidden (see GOOD function below). However, I'm totally clueless on how to calculate data by row when columns are hidden (see BAD function below). What I'm trying to figure out is highlighted in red in the attached spreadsheet.

GOOD
=SUMPRODUCT(SUBTOTAL(103,OFFSET(B\$2:B\$5,ROW(B\$2:B\$5)-MIN(ROW(B\$2:B\$5)),,1))*(B\$2:B\$5="Pass"))

BAD (returns 0 and includes a circular reference)
=SUMPRODUCT(SUBTOTAL(103,OFFSET(\$B2:\$F2,,COLUMN(\$B2:\$F2)-MIN(COLUMN(\$B2:\$F2)),1))*(\$B2:\$F2="Pass"))

## Excel 2007 :: Transposing Many Rows And Columns To One Single Column

Oct 22, 2013

I am trying to reformat the attached doc into one single column, It's a race listing of overseas marathons and I need it to look like this

Event Name
Event Date
City
Country
Entry fee
Website

in date order, one event after the other in a single column so I can flow it into a word doc ready to be designed. I need to apply in Excel (I am using 2007) to get this doc into the required format?!!

## Excel 2007 :: Inserting Number Of Blank Rows Between Cells

Mar 5, 2013

I have a worksheet with four data columns A,B,C,D starting in row 2. I want to add between every row, exactly 11 blank rows. So if we have:

Code:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

the end result should be:

Code:

A B C D

1 5 3 4
2 7 6 3
3 9 1 3

Do I necessarily need a macro for this? Or is there another quick and creative way to achieve this result?

## Excel 2007 :: Macro - Copying Column In Which Number Of Rows Can Vary

Sep 23, 2013

I am using MS Excel 2007 and I am trying to create a macro which will copy column E and paste the data in column E to the bottom of column D. However each week the amount of rows in these column with vary, they will always be the same amount of rows in column E as in column D but there may be 20 rows one week and a 100 rows the next.

The formula I currently have is below but this will only work for a specified number of rows. How I could change this to work for any number of rows?

Code:
Sub IPT()
'
' IPT Macro
'
'
Range("E1").Select

[Code] .....

## Excel 2003 :: Count Number Of Rows Between 2 Times

Feb 17, 2012

I am using excel 2003. My sheet contains Times in column A and numbers in column B. I want to be able to count how many of those rows fall into time periods. Such as how many are from 8:00-9:00 AM. I do have the time column formatted as Time.

Ex:
A1: 3:40 PM
A2: 8:20 AM
A3: 8:55 AM
A4: 10:23 AM
etc.

B1: 23
B2: 34
B3: 12
B4: 35
etc.

So I want to be able to pull that between 8 and 9 AM there are 2 records.

The second part would be (in another cell) to pull the SUM of the numbers that correspond to the previous question. In the previous example it would be the SUM of all corresponding cells (Column B) that fall within the 8 and 9 AM range. Which would be 46 in this example.

## Excel 2010 :: Count Consecutive Numbers Over Multiple Columns / Rows

May 27, 2014

I'm using Excel 2010 and my spreadsheet contains numbers in columns A:E and approx 500+ rows. Here is a 10 row example of my data:

A B C D E
0 1 2 3 4
5 6 7 8 9
0 2 4 6 8
1 3 5 7 9
1 2 4 5 8
3 4 5 6 9
9 8 1 2 3
7 6 1 4 0
0 8 2 1 9
1 0 5 3 2

I would like to count the number of consecutive times each number appears (to a max of 9 consecutive times in a row). So, from my example above:

Number 1 appears:
1 consecutive time = 1 (appears in row 1)
2 consecutive times = 1 (appears in rows 4 & 5)
3 consecutive times = 0
4 consecutive times = 1 (appears in rows 7, 8, 9 & 10)

Number 2 appears:
1 consecutive time = 4 (appears in row 1, row 3, row 5 & row 7)
2 consecutive times = 1 (appears in rows 9 & 10)
3 consecutive times = 0
4 consecutive times = 0

Number 5 appears:
1 consecutive time = 2 (appears in row 2 & row 10)
2 consecutive times = 0
3 consecutive times = 1 (appears in rows 4, 5 & 6)
4 consecutive times = 0

and so on....

## Excel 2007 :: Count Number Of Days Between Two Date Where Off Days Are Friday / Saturday And Holiday

Mar 14, 2014

I am making process TAT(Turn Around Time) which required following information. In Excel 2007.

1-Count number of days between two dates where working days are (Sun to Thursday). So required to exclude (Friday,Sat + Holidays)

A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 22
D1-Days between two dates 21
E1 To E10-Holidays

2-Count number of days between two dates where working days are (Sat to Thursday). So required to exclude (Friday + Holidays)

A1-Start Date Mar/01/2014
B1-End Date Mar/31/2014
C1-No Of Days 27
D1-Days between two dates 26
E1 To E10-Holidays

Note : Any weekend (off days) dates listed in holidays should not effect the query.

## Count Duplicate Rows And Produce Count #

Jul 21, 2008

I have a spreadsheet of over 15,000 lines of student information, sorted by student number. I want to count the number of rows which have a duplicate student number, up to 15 duplicates in a row, and show the total number of duplicates in a Separate Column. I.e.

Column 1 Column 2
Row 1 - 200101 3
Row 2 - 200101
Row 3 - 200101
Row 4 - 200102 2
Row 5 - 200102
Row 6 - 200103 1

I've been trying to use a Countif formula, but I found I had to use so many ANDs and ORs that the formula became too long. I don't know how to use programming code, only formulas in Excel. Is there an easier solution using some type of SUMPRODUCT code?

## Count Duplicate Rows Then Delete Duplicates

Mar 15, 2009

I have two columns of data, and I need to create a third column to count the number of times that same line appears in the document (and then remove all but the first copy of that line). my data looks as follows (and it is sorted so all duplicate rows appear directly next to each other):

I need this to look as follows:

## Duplicate Rows Based On Cell Count?

Nov 3, 2011

a quick macro/VBA project that would allow my to duplicate existing rows based on the count in one of the cells. For example, my data currently looks like this:

DCITEMITEMDESCVENDORCOST ABCSTATUSRCATOHExtended Cost 1st Discount Level 2nd Discount Level CGO6-IAVHORN12/24VDC MINI HORN07697\$ 12.69 UA24901\$ 12.69 \$ 9.99 \$ 5.99 CGBK-PR2AEPOXY-ENCAPSULATED RELAY W/ACT20775\$ 7.43 SA25203\$ 22.29 \$ 5.99 \$ 3.99 CGSF-SUB901CLEAR 2"SPACER F/STI 910507450\$ 7.88 SA25904\$ 31.51 \$ 5.99 \$ 3.99 CG0Q-BLTBWIR21OUT B/W IR BULLET 420TV 3.6M01047\$ 66.59 XD31211\$ 66.59 \$ 49.99 \$ 32.99

And I want to duplicate the rows based on the quanity in the OH column (highlighted in yellow) - so that it looks like this:

DCITEMITEMDESCVENDORCOST ABCSTATUSRCATOHExtended Cost 1st Discount Level 2nd Discount Level CGO6-IAVHORN12/24VDC MINI HORN07697\$ 12.69 UA24901\$ 12.69 \$ 9.99 \$ 5.99 CGBK-PR2AEPOXY-ENCAPSULATED RELAY W/ACT20775\$ 7.43 SA25203\$ 22.29 \$ 5.99 \$ 3.99

[Code] ........

## Excel 2007 :: Display Duplicate Values (Pivot Table)

Feb 13, 2012

I am trying to have duplicates and their corresponding data appear in my pivot table. Assume I have a list of over 1000 stores. Let's say store 101 appears twice in my data file.

When I create my pivot, the 101 only appears once and the data is consolidated. Is there a way to ungroup/display duplicates?

## Count Highest Number Of Duplicate Text Entries In Range

Nov 2, 2011

I have a column that is filled with different names. Some names appear more than once. There is one name in fact that appears four times.

I am looking for a formula, or VBA if that's necessary, that will look through the range Y2:Y50, and return a value of 4, because the person that has the most duplicates, her name appears 4 times.

If everyone's name appeared only once, except for a person whose name was in that range twice, then the formula would return a 2.

## Transpose Duplicate Rows To Columns

Mar 19, 2009

I have Excel 2007 and a two column lexicon in the following format:

word1 ; word_a
word1 ; word_b
word2 ; word_c
word2 ; word_d
word2 ; word_e
word3 ; word_f

would it somehow be possible to transpose it to:

word1 ; word_a ; word_b
word2 ; word_c ; word_d ; word_e
word3 ; word_f

## Excel 2007 :: Automatically Transfer Columns From One Worksheet To Another In Multiple Columns?

May 4, 2013

I would like to take the data from worksheet1 and put into worksheet2 but limit the length of a list (the real spread sheet has over 100 rows and i would like them in 4 sets of 25 versus the example I provided). Is there an array or macro that would make this work (keeping the formatting)..

Excel 2007
B
C
3
Name
Company

[Code].....

## Keep ONLY Duplicate Matter Number Rows

Oct 15, 2008

Is there a way to delete any rows that don't have a second occurrence of any cell in column a? I have unique numbers ONLY in column A. I want to tell Excel to.

a) Look at all the cells in column A ONLY (about 6,000 of them).
b) Look for any cells column a that have a duplicate entry in another cell.
c) Delete all rows that don't have a duplicate cell in column a.

This would be the entire row that I would like deleted. So every row should now only have the header info followed by 2 identical cells in column A. NOTE THAT THE OTHER COLUMNS WILL HAVE DIFFERENT INFO IN THEM, WHICH I WANT TO KEEP. I ONLY WANT THE CELL IN COLUMN A TO MATCH.....

## Delete Duplicate Rows Based Only 2 Columns.

Feb 18, 2010

I need to filter the following file.

I need to remove all rows where COL A value and COL B value are the same. COL C does not need to be considered. However I need to retain one of the Col C values for purposes of formatting.

The end result should look similar to columns F,G and H!

## Deleting Duplicate Rows When ALL Columns Are Identical

Aug 8, 2007

I found a useful resource on the web that gives a macro that deletes rows when the cells within a SINGLE column are identical. [url]

Does anyone know of a macro that can do more than that, one that will delete rows if ALL cells within ALL columns are identical?

For example, the macro should delete row 4 of the attached spreadsheet. It should leave row #2 there because it's the orginal row. But the duplicate row #4 should be deleted. The macro should leave row #5 there because not all columns are identical for that row.