MS Excel - pivot tables help needed
09-11-2010, 02:05 PM
#1
PassionFord Post Whore!!
Thread Starter
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?
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?
09-11-2010, 03:43 PM
#2
Advanced PassionFord User
Join Date: Feb 2005
Location: Solihull
Posts: 2,090
Likes: 0
Received 0 Likes
on
0 Posts
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
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
09-11-2010, 04:33 PM
#3
PassionFord Post Whore!!
Thread Starter
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.
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.
09-11-2010, 04:55 PM
#4
Advanced PassionFord User
Join Date: Feb 2005
Location: Solihull
Posts: 2,090
Likes: 0
Received 0 Likes
on
0 Posts
Oh ok, sorry I thought it was so you could create a merged spreadsheet.
You can try it in vba? Something like:
Haven't put it in to test and it's been a wee while so worth trying on a duplicate file first(!).
Chris
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
Chris
09-11-2010, 06:23 PM
#6
............
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
Thread
Thread Starter
Forum
Replies
Last Post
JoeyBoyden
Restorations, Rebuilds & Projects.
26
04-01-2024 02:36 PM
borboyous
Cars for Sale
6
22-08-2015 11:17 AM