Need a formula to match 2 fields, if no match then add blank 1

[dizzkat]

Hi,

I need a formula to match 2 fields, if field1 doesn't match field2, then add blank line in field1 until the fields match.

I have an excel document that has 2 fields that need to be matched up. Field1 is monthly totals and Field2 is yearly totals. Field1 may not an entry, but Field2 has them all. So, to match them up, I am inserting a blank and shifting cells down. This takes forever! I haven't been able to figure out how to do this in Excel XP. So, I am trying to use Crystal Reports 8.5. Any suggestions would be appreciated.

Many thanks!
Kathy

 

[checkai]

put field 1 into a formula @field1 and set it to

if table.field1 = table.field2 then
table.field1
else
''

Not sure if this is what you are looking for

 

[dizzkat]

Hi checkai,

It's hard to explain so here's how it looks...
CM# Des. Fees Cost Total CM#2 Des. YTDFees
1230 Cloth 32.50 20.40 52.90 1230 Cloth 3,407.50
1232 End1 16.25 76.18 92.43 1231 Splin 2,925.00
1235 Pipe 39.00 29.38 99.38 1232 End1 3,091.25
1238 Short 67.50 66.96 34.46 1233 End2 4,545.00
1245 Alum 60.00 10.33 50.33 1234 Comp 1,797.50
1246 Braz 50.00 65.00 50.00 1235 Pipe 8,102.50

This is how I want it to look...
CM# Des. Fees Cost Total CM#2 Des. YTDFees
1230 Cloth 32.50 20.40 152.90 1230 Cloth 3,407.50
1231 Splin 2,925.00
1232 End1 16.25 76.18 92.43 1232 End1 3,091.25
1233 End2 4,545.00
1234 Comp 1,797.50
1235 Pipe 39.00 9.38 399.38 1235 Pipe 8,102.50

I hope that makes sense. I think it's a lot more complicated than I thought. There's actually more fields involved, but I was hoping for a base formula I could work from. Let me know if you have any suggestions.
Thanks again,
Kathy

 

[janemaritz]

Can you put monthly in one worksheet and yearly in a second worksheet? Then you can treat the data as two separate tables that are joined by CM# and CM#2, with an outer join so that all records for CM#2 are shown even if there is no corresponding record for CM#. Use Crystal's Visual Linking Expert for the join.

 

[Madawc]

As janemaritz says, you really want CM#2, along with CM# if it exists. So treat it as a report of CM#2, with a 'Left outer' link to add CM# data when it exists.

In Crystal 8.5, at least, this can all be done using the Visual Linking Expert and the correct data should appear automatically.

Madawc Williams
East Anglia
Great Britain

 

[dizzkat]

That worked perfectly! Thank you all for your help. I believe the user has them in 2 different spreadsheets to begin with.