Formatting Sheet

[C]

I have a spreadsheet that contains Part Numbers and Supplier Data and other
information. The data is in the following format:
Col A Col B Col C:?
Part # Supplier Other Information in Rows c:? Could be aa or ZA
123 ABC
123 DEF
123 GHI
456 ABC
456 DEF

I would like to merge the data to look like this:

Col A Col B Other INformation C:?
123
ABC
DEF
GHI
456
ABC
DEF

One Part number may have 1 suppliers or 100 suppliers. The data in Col C:?
is supplier specific so it would need to remain on the same row as the
supplier. Any help would be greatly appreciated.

 

[Wouter HM]

Hi C,

You can try this:

Public Sub SplitOnPartnumber()
Do
If StrComp(ActiveCell.Value, ActiveCell.Offset(-1, 0).Value,
vbTextCompare) <> 0 Then
ActiveCell.EntireRow.Insert xlShiftDown
ActiveCell.Value = ActiveCell.Offset(1, 0).Value
ActiveCell.Font.Color = ActiveCell.Offset(1, 0).Font.Color
Else
ActiveCell.Font.Color = ActiveCell.Interior.Color
End If
ActiveCell.Offset(1, 0).Select
Loop Until IsEmpty(ActiveCell)
End Sub

Select the cell with the first part# and then start this macro.

HTH,

Wouter

 

[C]

Hi Wouter,

I tried this but get a syntax error on the first If statement. Any
recommendations?

 

[Wouter HM]

Hi C,

Please check in your VBE Tools --> References.
On top of the list thses four should be checked:

- Visual Basic for Applications
- Microsoft Excel n Object Library
- OLE Automation
- Microsoft Office n Object Library

Which version of Excel ar u using?


Wouter

 

[C]

Hi Wouter,

I checked and indeed these four are enabled/checked. I am using Excel 2003
SP3 Professional edition. I haven't had a chance to get back to this until
today. Was hoping to get your insight on they syntax error. I am a newbie
with this stuff and don't know what might be causing this. I copied your
post and get the error.

Thanks for your help,

 

[Wouter HM]

Hi C,

This part of my code should be on one line:

If StrComp(ActiveCell.Value, ActiveCell.Offset(-1, 0).Value,
vbTextCompare) <> 0 Then

Optionally you can use:

If StrComp(ActiveCell.Value, _
ActiveCell.Offset(-1, 0).Value, _
vbTextCompare) <> 0 Then


HTH,

Wouter