How? compare two worksheets and identify rows of data that appear on both.

Ok,

Suppose I have 2 worksheets.

Sheet 1 has 1000 lines of data spanning xNumber of columns.

Sheet 2 has 500 lines of data, also spanning the same number of columns.
An unknown number of identical lines of data appear on both sheets.

How can I get to a point where I can build one worksheet that is a composite 
of both sheets - such that no identical line of data appears twice on the 
final sheet?


Thanks in advance, Simon.


0
simon7270 (6)
1/9/2008 7:07:35 PM
excel 39879 articles. 2 followers. Follow

9 Replies
589 Views

Similar Articles

[PageSpeed] 57

You could do this several ways. It really depends on your data
integrity and what sort of data it is.

-- Use a VLOOKUP OR INDEX/MATCH formula to compare the 500 row ss
against the 1000 row ss. If you get a hit, delete the row, then paste
the two wkbks together.

OR

-- Paste the workbooks together, sort by column of your choice, then
use a procedure such as this to remove the duplicates.

Sub Deletedupes()
Dim rng As Excel.Range
Dim Col As Long
Dim X As Long
Dim r As Long
Application.ScreenUpdating =3D False

Set rng =3D ActiveSheet.UsedRange.Rows
Col =3D InputBox("What Column to use?" & vbCrLf & vbCrLf & "ex: for
column A, enter 1.")

X =3D rng.Rows.count

For r =3D X To 2 Step -1

    If Cells(r - 1, Col) =3D Cells(r, Col) Then
'        If Cells(r - 1, Col + 10) =3D Cells(r, Col + 10) Then
            Cells(r, Col).EntireRow.Delete
'        Else
'            Cells(r - 1, Col).EntireRow.Delete
'        End If
    End If
Next r

Application.ScreenUpdating =3D True
Application.StatusBar =3D False
End Sub



HTH,
JP

On Jan 9, 2:07=A0pm, "simon steel" <si...@srsteel.co.uk> wrote:
> Ok,
>
> Suppose I have 2 worksheets.
>
> Sheet 1 has 1000 lines of data spanning xNumber of columns.
>
> Sheet 2 has 500 lines of data, also spanning the same number of columns.
> An unknown number of identical lines of data appear on both sheets.
>
> How can I get to a point where I can build one worksheet that is a composi=
te
> of both sheets - such that no identical line of data appears twice on the
> final sheet?
>
> Thanks in advance, Simon.

0
jp2112 (204)
1/9/2008 7:29:57 PM
JP thanks for help.
i'll look at these - they should get me started.

Presume the second method is a macro?
any chance of a very quick overiew of what each bit of the macro is doing - 
I've little (almost none) experience with macros.

Simon


"JP" <jp2112@earthlink.net> wrote in message 
news:f50ea13f-f02a-4480-8bde-de58db965f62@k39g2000hsf.googlegroups.com...
You could do this several ways. It really depends on your data
integrity and what sort of data it is.

-- Use a VLOOKUP OR INDEX/MATCH formula to compare the 500 row ss
against the 1000 row ss. If you get a hit, delete the row, then paste
the two wkbks together.

OR

-- Paste the workbooks together, sort by column of your choice, then
use a procedure such as this to remove the duplicates.

Sub Deletedupes()
Dim rng As Excel.Range
Dim Col As Long
Dim X As Long
Dim r As Long
Application.ScreenUpdating = False

Set rng = ActiveSheet.UsedRange.Rows
Col = InputBox("What Column to use?" & vbCrLf & vbCrLf & "ex: for
column A, enter 1.")

X = rng.Rows.count

For r = X To 2 Step -1

    If Cells(r - 1, Col) = Cells(r, Col) Then
'        If Cells(r - 1, Col + 10) = Cells(r, Col + 10) Then
            Cells(r, Col).EntireRow.Delete
'        Else
'            Cells(r - 1, Col).EntireRow.Delete
'        End If
    End If
Next r

Application.ScreenUpdating = True
Application.StatusBar = False
End Sub



HTH,
JP

On Jan 9, 2:07 pm, "simon steel" <si...@srsteel.co.uk> wrote:
> Ok,
>
> Suppose I have 2 worksheets.
>
> Sheet 1 has 1000 lines of data spanning xNumber of columns.
>
> Sheet 2 has 500 lines of data, also spanning the same number of columns.
> An unknown number of identical lines of data appear on both sheets.
>
> How can I get to a point where I can build one worksheet that is a 
> composite
> of both sheets - such that no identical line of data appears twice on the
> final sheet?
>
> Thanks in advance, Simon.


0
simon7270 (6)
1/9/2008 7:35:08 PM
Sorry about that. Check out this site for more info on how to install.

http://www.rondebruin.nl/code.htm

What it does is first it asks you for what column you want to loop
through. Then it steps through the column and if it finds two rows
with the same information (presumably, because they are duplicates),
it deletes the first one. It is necessary to step backwards through
the rows when you are deleting, to ensure that rows aren't skipped.

Keep in mind that anything the macro does is permanent -- you should
make a backup of both workbooks and be absolutely sure you are doing
the right thing before allowing it to delete your data.

HTH,
JP


On Jan 9, 2:35=A0pm, "simon steel" <si...@srsteel.co.uk> wrote:
> JP thanks for help.
> i'll look at these - they should get me started.
>
> Presume the second method is a macro?
> any chance of a very quick overiew of what each bit of the macro is doing =
-
> I've little (almost none) experience with macros.
>
> Simon
>
> "JP" <jp2...@earthlink.net> wrote in message
>
> news:f50ea13f-f02a-4480-8bde-de58db965f62@k39g2000hsf.googlegroups.com...
> You could do this several ways. It really depends on your data
> integrity and what sort of data it is.
>
> -- Use a VLOOKUP OR INDEX/MATCH formula to compare the 500 row ss
> against the 1000 row ss. If you get a hit, delete the row, then paste
> the two wkbks together.
>
> OR
>
> -- Paste the workbooks together, sort by column of your choice, then
> use a procedure such as this to remove the duplicates.
>
> Sub Deletedupes()
> Dim rng As Excel.Range
> Dim Col As Long
> Dim X As Long
> Dim r As Long
> Application.ScreenUpdating =3D False
>
> Set rng =3D ActiveSheet.UsedRange.Rows
> Col =3D InputBox("What Column to use?" & vbCrLf & vbCrLf & "ex: for
> column A, enter 1.")
>
> X =3D rng.Rows.count
>
> For r =3D X To 2 Step -1
>
> =A0 =A0 If Cells(r - 1, Col) =3D Cells(r, Col) Then
> ' =A0 =A0 =A0 =A0If Cells(r - 1, Col + 10) =3D Cells(r, Col + 10) Then
> =A0 =A0 =A0 =A0 =A0 =A0 Cells(r, Col).EntireRow.Delete
> ' =A0 =A0 =A0 =A0Else
> ' =A0 =A0 =A0 =A0 =A0 =A0Cells(r - 1, Col).EntireRow.Delete
> ' =A0 =A0 =A0 =A0End If
> =A0 =A0 End If
> Next r
>
> Application.ScreenUpdating =3D True
> Application.StatusBar =3D False
> End Sub
>
> HTH,
> JP
>
0
jp2112 (204)
1/9/2008 8:01:04 PM
Ah...
Then it may not work.

not sure if you're say it will delete rows where for a given column, the 
data repeats.
i may have the same data in a cell in given column that is repeated in other 
cells in that column but that may not mean it is a duplicated line.
It is the rows where the whole row (some 15 cells across) is duplicated that 
I want to strip from the data.
Can you clarify if this is the outcome?

I'll try it tomorrow anyway and see what the effect is (Data is at work, I'm 
at home).

Simon



"JP" <jp2112@earthlink.net> wrote in message 
news:6bcd90b3-654d-46b9-90e0-729217ecba12@f3g2000hsg.googlegroups.com...
Sorry about that. Check out this site for more info on how to install.

http://www.rondebruin.nl/code.htm

What it does is first it asks you for what column you want to loop
through. Then it steps through the column and if it finds two rows
with the same information (presumably, because they are duplicates),
it deletes the first one. It is necessary to step backwards through
the rows when you are deleting, to ensure that rows aren't skipped.

Keep in mind that anything the macro does is permanent -- you should
make a backup of both workbooks and be absolutely sure you are doing
the right thing before allowing it to delete your data.

HTH,
JP


On Jan 9, 2:35 pm, "simon steel" <si...@srsteel.co.uk> wrote:
> JP thanks for help.
> i'll look at these - they should get me started.
>
> Presume the second method is a macro?
> any chance of a very quick overiew of what each bit of the macro is 
> doing -
> I've little (almost none) experience with macros.
>
> Simon
>
> "JP" <jp2...@earthlink.net> wrote in message
>
> news:f50ea13f-f02a-4480-8bde-de58db965f62@k39g2000hsf.googlegroups.com...
> You could do this several ways. It really depends on your data
> integrity and what sort of data it is.
>
> -- Use a VLOOKUP OR INDEX/MATCH formula to compare the 500 row ss
> against the 1000 row ss. If you get a hit, delete the row, then paste
> the two wkbks together.
>
> OR
>
> -- Paste the workbooks together, sort by column of your choice, then
> use a procedure such as this to remove the duplicates.
>
> Sub Deletedupes()
> Dim rng As Excel.Range
> Dim Col As Long
> Dim X As Long
> Dim r As Long
> Application.ScreenUpdating = False
>
> Set rng = ActiveSheet.UsedRange.Rows
> Col = InputBox("What Column to use?" & vbCrLf & vbCrLf & "ex: for
> column A, enter 1.")
>
> X = rng.Rows.count
>
> For r = X To 2 Step -1
>
> If Cells(r - 1, Col) = Cells(r, Col) Then
> ' If Cells(r - 1, Col + 10) = Cells(r, Col + 10) Then
> Cells(r, Col).EntireRow.Delete
> ' Else
> ' Cells(r - 1, Col).EntireRow.Delete
> ' End If
> End If
> Next r
>
> Application.ScreenUpdating = True
> Application.StatusBar = False
> End Sub
>
> HTH,
> JP
> 


0
simon7270 (6)
1/9/2008 8:30:10 PM
The idea is that, if you have true duplicates (i.e. two rows with the
exact same information in each cell), and the worksheet is sorted, the
code would compare one cell from each row and if they are the same,
delete one. i.e. if you had a list of addresses, you could compare the
street number column.

Hopefully this will give you a visual representation of what would
happen.

http://www.cpearson.com/excel/deleting.htm


HTH,
JP

On Jan 9, 3:30=A0pm, "simon steel" <si...@srsteel.co.uk> wrote:
> Ah...
> Then it may not work.
>
> not sure if you're say it will delete rows where for a given column, the
> data repeats.
> i may have the same data in a cell in given column that is repeated in oth=
er
> cells in that column but that may not mean it is a duplicated line.
> It is the rows where the whole row (some 15 cells across) is duplicated th=
at
> I want to strip from the data.
> Can you clarify if this is the outcome?
>
> I'll try it tomorrow anyway and see what the effect is (Data is at work, I=
'm
> at home).
>
> Simon
>
> "JP" <jp2...@earthlink.net> wrote in message
>
> news:6bcd90b3-654d-46b9-90e0-729217ecba12@f3g2000hsg.googlegroups.com...
> Sorry about that. Check out this site for more info on how to install.
>
> http://www.rondebruin.nl/code.htm
>
> What it does is first it asks you for what column you want to loop
> through. Then it steps through the column and if it finds two rows
> with the same information (presumably, because they are duplicates),
> it deletes the first one. It is necessary to step backwards through
> the rows when you are deleting, to ensure that rows aren't skipped.
>
> Keep in mind that anything the macro does is permanent -- you should
> make a backup of both workbooks and be absolutely sure you are doing
> the right thing before allowing it to delete your data.
>
> HTH,
> JP
>
> On Jan 9, 2:35 pm, "simon steel" <si...@srsteel.co.uk> wrote:
>
>
>
> > JP thanks for help.
> > i'll look at these - they should get me started.
>
> > Presume the second method is a macro?
> > any chance of a very quick overiew of what each bit of the macro is
> > doing -
> > I've little (almost none) experience with macros.
>
> > Simon
>
> > "JP" <jp2...@earthlink.net> wrote in message
>
> >news:f50ea13f-f02a-4480-8bde-de58db965f62@k39g2000hsf.googlegroups.com...=

> > You could do this several ways. It really depends on your data
> > integrity and what sort of data it is.
>
> > -- Use a VLOOKUP OR INDEX/MATCH formula to compare the 500 row ss
> > against the 1000 row ss. If you get a hit, delete the row, then paste
> > the two wkbks together.
>
> > OR
>
> > -- Paste the workbooks together, sort by column of your choice, then
> > use a procedure such as this to remove the duplicates.
>
> > Sub Deletedupes()
> > Dim rng As Excel.Range
> > Dim Col As Long
> > Dim X As Long
> > Dim r As Long
> > Application.ScreenUpdating =3D False
>
> > Set rng =3D ActiveSheet.UsedRange.Rows
> > Col =3D InputBox("What Column to use?" & vbCrLf & vbCrLf & "ex: for
> > column A, enter 1.")
>
> > X =3D rng.Rows.count
>
> > For r =3D X To 2 Step -1
>
> > If Cells(r - 1, Col) =3D Cells(r, Col) Then
> > ' If Cells(r - 1, Col + 10) =3D Cells(r, Col + 10) Then
> > Cells(r, Col).EntireRow.Delete
> > ' Else
> > ' Cells(r - 1, Col).EntireRow.Delete
> > ' End If
> > End If
> > Next r
>
> > Application.ScreenUpdating =3D True
> > Application.StatusBar =3D False
> > End Sub
>
> > HTH,
> > JP- Hide quoted text -
>
> - Show quoted text -

0
jp2112 (204)
1/9/2008 10:19:50 PM

"simon steel" <simon@srsteel.co.uk> wrote in message 
news:38KdnSO6AbuihhjanZ2dnUVZ8radnZ2d@pipex.net...
> Ok,
>
> Suppose I have 2 worksheets.
>
> Sheet 1 has 1000 lines of data spanning xNumber of columns.
>
> Sheet 2 has 500 lines of data, also spanning the same number of 
> columns.
> An unknown number of identical lines of data appear on both sheets.
>
> How can I get to a point where I can build one worksheet that is a 
> composite of both sheets - such that no identical line of data appears 
> twice on the final sheet?
>
>
> Thanks in advance, Simon.
>
>
Have you tried Excel's compare option (see Help).

Briefly, if you open both Worksheets, there will be an option under 
Window menu to compare A with B.

 


0
1/10/2008 12:04:12 PM
Dear Simon,
You can try  some useful add-ins for Excel that I prefer to use in the 
similar cases. To compare the sheets, use this: 
http://www.office-excel.com/excel-addins/compare-spreadsheets.html and to 
unite the sheets, use this one - 
http://www.office-excel.com/excel-addins/advanced-consolidation-manager.html
Hope it helps.
Regards,
Evgeny

"simon steel" <simon@srsteel.co.uk> wrote in message 
news:38KdnSO6AbuihhjanZ2dnUVZ8radnZ2d@pipex.net...
> Ok,
>
> Suppose I have 2 worksheets.
>
> Sheet 1 has 1000 lines of data spanning xNumber of columns.
>
> Sheet 2 has 500 lines of data, also spanning the same number of columns.
> An unknown number of identical lines of data appear on both sheets.
>
> How can I get to a point where I can build one worksheet that is a 
> composite of both sheets - such that no identical line of data appears 
> twice on the final sheet?
>
>
> Thanks in advance, Simon.
>
> 


0
1/10/2008 4:33:26 PM
Thanks to all who have offered help!

plenty for me to go at here.

simon


"Evgeny Grischenko" <evg_grischenko@mail.ru> wrote in message 
news:ejECJa6UIHA.1212@TK2MSFTNGP05.phx.gbl...
> Dear Simon,
> You can try  some useful add-ins for Excel that I prefer to use in the 
> similar cases. To compare the sheets, use this: 
> http://www.office-excel.com/excel-addins/compare-spreadsheets.html and to 
> unite the sheets, use this one - 
> http://www.office-excel.com/excel-addins/advanced-consolidation-manager.html
> Hope it helps.
> Regards,
> Evgeny
>
> "simon steel" <simon@srsteel.co.uk> wrote in message 
> news:38KdnSO6AbuihhjanZ2dnUVZ8radnZ2d@pipex.net...
>> Ok,
>>
>> Suppose I have 2 worksheets.
>>
>> Sheet 1 has 1000 lines of data spanning xNumber of columns.
>>
>> Sheet 2 has 500 lines of data, also spanning the same number of columns.
>> An unknown number of identical lines of data appear on both sheets.
>>
>> How can I get to a point where I can build one worksheet that is a 
>> composite of both sheets - such that no identical line of data appears 
>> twice on the final sheet?
>>
>>
>> Thanks in advance, Simon.
>>
>>
>
> 


0
simon7270 (6)
1/10/2008 8:11:30 PM
Good luck Simon, hope you are able to find what you need.

Thx,
JP

On Jan 10, 3:11=A0pm, "simon steel" <si...@srsteel.co.uk> wrote:
> Thanks to all who have offered help!
>
> plenty for me to go at here.
>
> simon
>
0
jp2112 (204)
1/10/2008 9:16:14 PM
Reply:

Similar Artilces:

compare
Hello everyone, I have been reading this group for a while, however I am quite a rookie in using of excel. I would appreciate if someone could help with this issue. I quite often have to compare two sheets (from different workbooks-files, but with the same sheet name). Calculations are thus updated time by time and I need to check where were the main differences. Cells contain both values and formulas. I have found through this newsgroup nice add-ins of Myrna Larson and Bill Manville, and Rob Bruce. However I would need the macros to highlight only significant differences (let's say fro...

Remove multiple rows of Null fields in a report
I have created a report with multiple lines of data that could be null. I set the fields to "can shrink." It does shrink the line and move it up a field but if I have multiple fields in a row it does not bring the field all the way to the top. Any ideas? Brad wrote: >I have created a report with multiple lines of data that could be null. I set >the fields to "can shrink." It does shrink the line and move it up a field >but if I have multiple fields in a row it does not bring the field all the >way to the top. A CanShrink text box will shrin...

How to make two codes "Worksheet_Change" work together in same sheet code page
Friends, Please, anybody knows how to make these two VB codes work together? When I put them together in the same "sheet code page" in VBA, th second one doesn't work. Why? *** Code 1 *** Private Sub Worksheet_Change(ByVal Target As Range) * * On Error GoTo QuitCode * * If Intersect(Target, Range("c1:c15")) Is Nothing Then * * * * Exit Sub * * ElseIf Target.Value <>*"" And Target.Offset(0, -1).Value = "" Then * * * * MsgBox "You haven't typed the name of the client yet." * * * * Target.Offset(0, -1).Activate End If QuitCode: ...

Array to single row -- Any simple way to do this?
Is there an obvious formula to consolidate alphanumeric data across an array of multiple rows and columns into one row of values which have appeared at least once in the array (i.e., no duplicates)? Example: A B C 1 Apple Pear Orange 2 Grape Apple Pear 3 Melon Orange Grape Result: Apple, Orange, Pear, Grape, Melon I would also only like to show a value if it appears at least x times across the array. Example: (Must appear at least 2 times) Result: Apple, Orange, Pear, Grape Thanks for any and all help. CB Hi any chance you could...

Two Email Accounts not able to reply out of one of them
Hello All, I was wondering if someone here has ran into this problem. I currently have two email accounts setup in Exchange I can receive email in both of these accounts and view the email in Outlook 2003. In my default account if I receive an email I can reply back. But in my secondary mailbox if I receive an email I cannot reply back I get a error message which is listed below. On the secondary account I have added my username under permissions and gave it full access but I am still getting the below error. Anything will help thanks. Your message did not reach some or all of the inten...

How do I build a workbook from the worksheets another workbook?
Is it possible to make up the sheets of an Excel workbook from another workbook that is in a remote address (i.e. a folder below) If you're asking whether you can copy (or move) those sheets from that remote workbook into another workbook, then the answer is yes. If you're asking if a workbook can contain worksheets of another workbook, then the answer is no. Rico wrote: > > Is it possible to make up the sheets of an Excel workbook from another > workbook that is in a remote address (i.e. a folder below) -- Dave Peterson Thanks Dave, I know you can populate cells from...

Two databases or one
Hi, What if you have two organizations and you plan to design a database to do the same task but within that task, the defined tables will vary because of unique qualities related to the individual organization. Would it be best to create separate databases for each organization or keep the two organizations together? Anyone know the guidelines related to this? Thanks for any feedback. Ask yourself this - How often will you need the combined data? -- Build a little, test a little. "AccessKay" wrote: > Hi, > > What if you have two o...

Outlook 2003 appearance
I really dislike the look with the panes/panels of Outlook 2003. Is it possible to select an Outlook 2002/XP look or will I have to go back to Office XP for that? Wouter, I dont' like the new look either, but you can change the individual items of the GUI and when you do that, it comes close to the old look. Just my 2 cents worth. Eric "Wouter" <anonymous@discussions.microsoft.com> wrote in message news:066001c3a330$cd235b70$a301280a@phx.gbl... > I really dislike the look with the panes/panels of Outlook > 2003. Is it possible to select an Outlook 2002/XP lo...

Convert Rows data
Hi there, I have the following sample data: PARTNUMBER QTY RATe PRUN VALID_FROM_VALID_TO ZWR1 0.1095 USD 1 20050512 20050526 XREZ12 0.1095 USD 1 20050527 99991231 is it possible to put this data in the following: 20050512 20050526 20050527 99991231 ZWR1 XREZ12 0.1095 0.1095 USD USD 1 1 So basically make the exis...

Insert / remove a row from protected sheet excel 2003 #2
Hi, I have my worksheet protected. I set the frist 5 rows all cells to Locked. The rest of the cells are Unlocked. When applying protection I ticked the boxes saying allow insert of row and allow delete rows. Yet when the protection is applied the icons / menu options for inserting / deleting rows are grayed out. What am I doing wrong? Thanks! Michiel. ...

Comparing
I have two columns of numbers column A could be up to 3500 rows. Column B may be more or less than 3500 rows. What I'm trying to do is see if any number in columns "B" appears anywhere in Column "A", and if so we can just highlight it in both columns. Actually I would want to be able to sort by highlighted. so maybe add someway to sort by matched or unmatched. I hope this makes sense. In column C put... =IF(ISNA(VLOOKUP($A1,$B:$B,1,FALSE)),"Not in B","In B") In column D put... =IF(ISNA(VLOOKUP($B1,$A:$A,1,FALSE)),"Not in ...

how to create formula to divide two rows autoaatically
Is there any way to setup a sheet or create a formula so that it will divide the data in column A by Column B anytime the data is entered and put it into columnC ? What I am trying to do is create a spreadsheet for calculating fuel MPG. So I have columns as miles, gallons and the calculation as MPG. What I want to do is anytime a value is entered into miles and gallons, to calculate mpg and put it into that respective cell. Is there any way I can do this ? Thanks -- Tony Tony, in Column C, type the formula =sum(a1/b1). This should give you the result you are looking for. Hop...

2 workbooks
I have got 2 workbooks with a list of names. I need to find out if a name appears in both books. One of the workbooks came from a different source and the other one is a report I ran from our database. I need to find out whether the workbook from out of the company has got any of our own names on it. I am using Microsoft Excel 97 and am fairly new at this so please be gentle. One way I did think was to combine the two workbooks into one and find the duplicates but thought there may be another way. Thank you. Hi "queen on", Assume that In book1 the names are in column A, s...

Data Markers
Can I change data markers so that it reflects the conditional formatting on that cell? For example if the data in column c is being plotted and the conditional formatting I have on the cells are if column D = "Y" can I have it so that those markers are plotted with a circle marker and the rest with a square marker? Thanks! Hi, Have a look at Jon's page for conditional charts. http://peltiertech.com/Excel/Charts/ConditionalChart1.html Cheers Andy ivy_gayle wrote: > Can I change data markers so that it reflects the conditional formatting on > that cell? For exampl...

Help on {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}
Dear All, Plz help on this formula {=SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))}, how this works as I m new excel user. what does is meant $A$1:$A$7=$A$10 Hi As an array formula, it is testing each cell in the range A1 to A7 to see if it has the same value as that in cell A10 and returning what is the first row number that the value occurs in the range. -- Regards Roger Govier "Sandeep Jangra" <SandeepJangra@discussions.microsoft.com> wrote in message news:9A133D2D-75A6-4A3B-93AB-6680D164C647@microsoft.com... > Dear All, > Plz help on this formula > ...

Compare two files and update data from another file base on words in a cell separated by commas
I have two file with several colomns. I need to compare two Col B fileA Col B of FileB as shown in example. http://spreadsheets.google.com/ccc?key=0AgUVfFOnkiaKdFBiNDFLamcybXdhW... Each col have about 1000 rows. Each row contains thousands of words and phrases separated by Comma. As you can see from example, my data has soo many words and phrases separated by comma in each row of two colomn A and B. ============== i Need to merge data of corresponding row from COLA$FileB TO corresponding row of COLA$FileB Also merge data of corresponding row from COLB$FileB TO correspondin...

differences between two tables
I'm trying to find the differences between two tables, A and B. There are some records in A that are not in B, there are some in B that are not in A, and there are some in both but with differences in the fields. I'm not getting everything. Does anyone have any ideas how I can tackle this? Thanks! Use a UNION ALL query. -- KARL DEWEY Build a little - Test a little "denise" wrote: > I'm trying to find the differences between two tables, A and B. There are > some records in A that are not in B, there are some in B that are not in A, > and there are s...

need to make cell blank if no data in that row
I have a data in columns, I want data to be shown only if there is data in that row if no data then leave it blank (here is what I am asking for) Stock trade Gain/Loss Gain/Loss all trades abc 4.18% 4.18% def 3.77% 7.95% [no trade] N/A 7.95% [no trade] N/A 7.95% i want trade column to appear blank in cell of gain/loss for all trade instead of showing 7.95%. if no trade made If there is no data but the formula is in column, third line shows 7.95%, 4th line shows 7.95%, 5th line 7.95...

two tables with same field names
I create an order status table for my division each week by running queries on corporate's systems. The field names are always the same, and there lies my problem. I need to measure the change in promise dates weekly to the order line level. For example, I need to pull last monday's promise date minus this monday's promise date to get the change in number of days. Post your table structure so a query can be assembled. -- KARL DEWEY Build a little - Test a little "deb" wrote: > I create an order status table for my division each week by running queries > o...

can I make a field data type a choice ("x" or "y") instead yes/no
in Access 2003 I would like to enter a choice in a table data type so I can have a two choice ("x" or "y") ro a multiple choice ("a" or "b" or "c"). How can I do this? You need to create a related table, so you can have as many choices as apply. Take a look at this article: http://allenbrowne.com/casu-23.html It uses a sport example, where a student may be involved in several sports. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rath...

Why does Outlook open two windows?
When installed Outlook 2003 with a new profile about two hours ago, it would open one window each time I started Outlook. But now, after I've been doing a lot of work on the folder structure, particularly transferring dozens of folders by drag and drop within a single non-default PST file. I notice that the computer has been very noisy for a while, apparently furiously coping with the changes I've been making. Now when I start Outlook, it opens two windows; presumably that's an outcome of something I inadvertently did when transferring all the folders. I'd rather it ...

Keyboard Shortcut Key for Switching between Worksheets In a Workbook
Ok, I'm pretty sure this feature existed (as I recalled using it in Excel several versions ago). Now that I need to work with lots of data in different worksheets in a workbook, I find myself severely wanting this keyboard shortcut key (being an old school user) again, instead of moving my mouse to the desired tab below and clicking it (which is wasting too much of my time. A search in Excel help came up with nothing. So I think it's being taken out of official documentation. Anyone knows the correct key combination for this? Greatly appreciated. :) ________ Clifford C...

1-way data sync into CRM (v3)
How do I determine if I need to purchase Scribe (and training) or write a customized web application using web service calls? (Any classes on that?) How do I know which would be less expensive now and in the long run? I have no knowledge of Scribe at this time, and I am not familiar with the SDK / C#. Issue: I need a one-way synchronization of data from a SQL database app into CRM for Leads, Opportunities, and Contacts. That data needs to be in CRM only for management reporting purposes and will not be updated by users. This will be an ongoing daily update process. The project man...

Appending worksheets
Hello, I have a huge workbook with some 200 worksheets (Excel 2002) and wouldlike to create one single worksheet by appending the data on all worksheets one after another. Is there an easy way to do this without programming a macro? Thanks so much. Provided the total data rows do not exceed 65535 (assuming a standard top row for col labels), one way would be sequential manual copy > paste into a single new sheet placed to the left of the 200 source sheets. At an est 15 sec per manual op, 200 sheets would only take roughly an hour of work to accomplish. Thereafter, to clean up, jus...

need Excel formula that figures out which data adds up to total
I have 33 different dollar amounts listed along with their invoice numbers and I need to figure out how many of those amounts add up to a check that I received. For example, I have 33 different rows of numbers in 1 column that add up to 53,545.69. Which rows of numbers add up to that? I received a check for $11,646.54 but do not know which invoices it pays. Is there an Excel formula that can tell me which ones could add up to that total? Thanks for any help, Kerri Google for SOLVER "Kerri" wrote: > I have 33 different dollar amounts listed along with th...