MS Excel - pivot tables help needed

Reply Subscribe

Thread Tools

Search this Thread

Nov 9, 2010 | 02:05 PM

Iain Mac

Thread Starter

PassionFord Post Whore!!

Joined: Jun 2003

Posts: 4,903

Likes: 6

From: Scotland

MS Excel - pivot tables help needed

I have two versions of the same spreadsheet that contain about 95% the same information, but one of them will have new additions or alterations.

I believe that a Pivot Table is the quickest way to find these new entries but haven't got a clue where to start!

Anyone able to offer a step-by-step guide?

Nov 9, 2010 | 03:43 PM

cjwood555

Advanced PassionFord User

Joined: Feb 2005

Posts: 2,090

Likes: 0

From: Solihull

Is there a unique indentifier for each row?

If so, the quickest and easiest way is to copy and paste both into one worksheet, one after the other, Select All, then:

- Data
- Filter
- Advanced Filter
- OK if it gives an error
- Criteria range - select just the unique identifier field
- Tick 'Unique records only'
- Ok
- Select all, copy and paste into new sheet, then you have your merged sheets.

HTH
Chris

Nov 9, 2010 | 04:33 PM

Iain Mac

Thread Starter

PassionFord Post Whore!!

Joined: Jun 2003

Posts: 4,903

Likes: 6

From: Scotland

Chris

Thanks for this but I don't think I've explained properly - the method above has given me 18,440 unique records from 38,693 lines in the combined sheet.

The first version has 19,184 lines, and the second has 19,510 lines - its the 326 new lines I'm wanting to identify.

Nov 9, 2010 | 04:55 PM

cjwood555

Advanced PassionFord User

Joined: Feb 2005

Posts: 2,090

Likes: 0

From: Solihull

Oh ok, sorry I thought it was so you could create a merged spreadsheet.

You can try it in vba? Something like:

Code:

Dim a = "New Worksheet Name"
Dim b = "Old Worksheet Name" 
Dim x = 1 //Column number for unique ID

With Worksheets(a)

  for i = 0 to .Rows.Count
    for j = 0 to Worksheets(b).Rows.Count
      if Worksheets(b).Cells(j, x).Value = .Cells(i, x).Value then .Rows(i).Delete
     end for
  end for

end with

Haven't put it in to test and it's been a wee while so worth trying on a duplicate file first(!).

Chris

Nov 9, 2010 | 05:03 PM

Iain Mac

Thread Starter

PassionFord Post Whore!!

Joined: Jun 2003

Posts: 4,903

Likes: 6

From: Scotland

Crikey - you've forgotten more than I'll ever know about this stuff because I'm afraid you lost me at vba!!!

Nov 9, 2010 | 06:23 PM

It's Czech Mate

............

Joined: Jun 2003

Posts: 12,970

Likes: 103

From: West Mids

If there is a unique identifier use a vlookup if not combine a few rows and make a unique id then use a vlookup. If u need help, mail them to me and I'll show you