Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging cells and eliminating spaces for empty cells | Excel Discussion (Misc queries) | |||
Formula for merging cells/re sizing cells | Excel Discussion (Misc queries) | |||
merging cells together but keeping all data from the cells | Excel Discussion (Misc queries) | |||
Selecting and merging range within numerous rows | Excel Programming | |||
Merging Cells but have each cell counted in the range of merged c. | Excel Worksheet Functions |