Need help with merging a range of cells
 

I have a spreadsheet that is generated via a C# program. In the spreadsheet,
for example, column A will contain unit numbers. Each unit may have several
products associated with it. So for example the spreadsheet will look like
this.

Unit # Team Leader Product Manufacturer
258 John Smith SONY
258 John Smith LG
258 John Smith MITSUBISHI
659 Chris Jones MAYTAG
659 Chris Jones KENMORE
659 Chris Jones AMANA
659 Chris Jones GE
8241 Sean White MOTOROLA
8241 Sean White SAMSUNG
8241 Sean White LG
8241 Sean White NOKIA

My problem is that I am trying to merge a range of cells. I only want one
unit number to show up and one team leader. So the spreadsheet would look
like this instead......Can someone give me some pointers? Thanks!

Unit # Team Leader Product Manufacturer
258 John Smith SONY
LG
MITSUBISHI
659 Chris Jones MAYTAG
KENMORE
AMANA
GE
8241 Sean White MOTOROLA
SAMSUNG
LG
NOKIA

Need help with merging a range of cells
 

Crappy Hunter College computers!! This thing is soooo sloooow; it froze and
somehow posted to the wrong discussion!!

Anyway..........the easiest way would be to use a Pivot Table. It would
take about 5-10 seconds to get the results you want. See this link for info:
http://www.cpearson.com/excel/pivots.htm

Place all your date elements in the Row field. Double-click each field (at
the top of the Row) and choose Subtotals None Ok. If it doesn't work
for you at first, try a couple times. I know it works. it is a little
tricky the first couple times you deal with it though...

Regards,
Ryan---


--
RyGuy


"New2This" wrote:

I have a spreadsheet that is generated via a C# program. In the spreadsheet,
for example, column A will contain unit numbers. Each unit may have several
products associated with it. So for example the spreadsheet will look like
this.

Unit # Team Leader Product Manufacturer
258 John Smith SONY
258 John Smith LG
258 John Smith MITSUBISHI
659 Chris Jones MAYTAG
659 Chris Jones KENMORE
659 Chris Jones AMANA
659 Chris Jones GE
8241 Sean White MOTOROLA
8241 Sean White SAMSUNG
8241 Sean White LG
8241 Sean White NOKIA

My problem is that I am trying to merge a range of cells. I only want one
unit number to show up and one team leader. So the spreadsheet would look
like this instead......Can someone give me some pointers? Thanks!

Unit # Team Leader Product Manufacturer
258 John Smith SONY
LG
MITSUBISHI
659 Chris Jones MAYTAG
KENMORE
AMANA
GE
8241 Sean White MOTOROLA
SAMSUNG
LG
NOKIA

Need help with merging a range of cells
 

Well I'm trying to accomplish this in the C# program that generates the
spreadsheet. If I understand correctly, the pivot table is created via the
Excel application, correct?

"ryguy7272" wrote:

Crappy Hunter College computers!! This thing is soooo sloooow; it froze and
somehow posted to the wrong discussion!!

Anyway..........the easiest way would be to use a Pivot Table. It would
take about 5-10 seconds to get the results you want. See this link for info:
http://www.cpearson.com/excel/pivots.htm

Place all your date elements in the Row field. Double-click each field (at
the top of the Row) and choose Subtotals None Ok. If it doesn't work
for you at first, try a couple times. I know it works. it is a little
tricky the first couple times you deal with it though...

Regards,
Ryan---


--
RyGuy


"New2This" wrote:

I have a spreadsheet that is generated via a C# program. In the spreadsheet,
for example, column A will contain unit numbers. Each unit may have several
products associated with it. So for example the spreadsheet will look like
this.

Unit # Team Leader Product Manufacturer
258 John Smith SONY
258 John Smith LG
258 John Smith MITSUBISHI
659 Chris Jones MAYTAG
659 Chris Jones KENMORE
659 Chris Jones AMANA
659 Chris Jones GE
8241 Sean White MOTOROLA
8241 Sean White SAMSUNG
8241 Sean White LG
8241 Sean White NOKIA

My problem is that I am trying to merge a range of cells. I only want one
unit number to show up and one team leader. So the spreadsheet would look
like this instead......Can someone give me some pointers? Thanks!

Unit # Team Leader Product Manufacturer
258 John Smith SONY
LG
MITSUBISHI
659 Chris Jones MAYTAG
KENMORE
AMANA
GE
8241 Sean White MOTOROLA
SAMSUNG
LG
NOKIA

Need help with merging a range of cells
 

Hi,

In VBA (Not in C#) but it should give you an idea as to how to accomplish
what you want. You will see that I have done it in 2 stages. One to set the
background color of the cells to be cleared and then clear the colored cells.
See the comment re stopping the program if you want to see what is being
cleared.

Hope it helps.

Sub RemoveSuperfluous()
Dim rngUnit As Range
Dim c As Range

With Sheets("Sheet1")
Set rngUnit = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

For Each c In rngUnit
If c = c.Offset(1, 0) Then
'Set interior color for both Unit# and Team Leader
Range(c, c.Offset(0, 1)).Interior.ColorIndex = 6
End If
Next c

'Stop 'Stop here if you want to test what is being cleared

For Each c In rngUnit
If c.Interior.ColorIndex = 6 Then
'Clear contents only for both Unit# and Team Leader
Range(c, c.Offset(0, 1)).ClearContents

'Remove ColorIndex for both Unit# and Team Leader
Range(c, c.Offset(0, 1)).Interior.ColorIndex = xlNone
End If
Next c

End Sub

--
Regards,

OssieMac

Need help with merging a range of cells
 

My apologies. I made some changs to the program after initial testing and
then did not look at the results carefully enough after making those changes.
Replace it with the following code.

Sub RemoveSuperfluous()
Dim rngUnit As Range
Dim c As Range

With Sheets("Sheet1")
Set rngUnit = .Range(.Cells(2, "A"), _
.Cells(.Rows.Count, "A").End(xlUp))
End With

For Each c In rngUnit
If c = c.Offset(1, 0) Then
'Set interior color for both Unit# and Team Leader
Range(c.Offset(1, 0), c.Offset(1, 1)).Interior.ColorIndex = 6
End If
Next c

'Stop 'Stop here if you want to test what is being cleared

For Each c In rngUnit
If c.Interior.ColorIndex = 6 Then
'Clear contents only for both Unit# and Team Leader
Range(c, c.Offset(0, 1)).ClearContents

'Remove ColorIndex for both Unit# and Team Leader
Range(c, c.Offset(0, 1)).Interior.ColorIndex = xlNone
End If
Next c

End Sub

--
Regards,

OssieMac