cancel
Showing results for 
Search instead for 
Did you mean: 

Merging rows and removing duplicates

Former Member
0 Kudos

Hi,

I have two sheets Sheet1 and Sheet 2. I want my output to be like sheet 3. I have a larger data to work with. Appreciate the help. I need this to be done on crystal. I connect it to my Excel.

sheet1

item no       sales

a                   4

b                   5

c                   7

b                   9

d                   5

sheet 2

item no         purchases

a                       3

b                        5

d                        6

e                        8

e                         1

sheet 3       

item no           sales         purchases

a                     4                  3

b                     14                 5

c                     7          

d                      5                  6

e                                          9

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

HI,

Please try the below query in add command suggested by Abhilash, this would have good performance

select x.itemno, sum(x.sales), sum(x.purchase)

from
(
Select itemno, sales, 0 as purchase from table1

union all

Select itemno, 0 as sales, purchase from table2
)x

group by x.itemno

Cheers,

Kiran

abhilash_kumar
Active Contributor
0 Kudos

Hi Iti,

You need a Full Outer Join to be able see records like Sheet 3.

Since Full Outer Join is not available with all datasources the other alternative is to create a Command Object (via the Add Command option) and use a SQL query like this:

Select * from table 1 LEFT OUTER JOIN table2 on table1.field1 = table2.field2

UNION

Select * from table 1 RIGHT OUTER JOIN table2 on table1.field1 = table2.field2

-Abhilash

Former Member
0 Kudos

Thanks. I am new to crystal. Where do i need to go to add this query. I have my crystal open and connected to my excel.

abhilash_kumar
Active Contributor
0 Kudos

Go to the Database Expert > Under the Connection Name of your Excel sheet you should have an option called 'Add Command'. Select Add Command and create a query similar to what I suggested above.

-Abhilash