New Excel Forum

This forum has been moved to TeachExcel.com

Ask all future questions in the New Excel Forum.

ExcelKey

Normalizing data on a Bill of Material

Macros, VBA, Excel Automation, etc.

Re: Normalizing data on a Bill of Material

Postby PSB » Mon Dec 12, 2011 8:55 pm

Hi Don,
Sorry for any confusion I may have caused. I tried to give a simple example for ease of communication. I was thinking it didn't matter how many columns were in a spreadsheet since each row would be copied in its entirety. It sounds like that was a bad assumption on my part. The issue is I will get different number of columns from different customers. In addition, there will be different data in some cases. Prior to running your code I will do a manual "normalization" for each spreadsheet by moving the reference designators to column A and the quantity to column C. Thanks, Pat
  • 0

PSB
Rookie
 
Posts: 16
Joined: Dec 9, 2011
Reputation: 0

Re: Normalizing data on a Bill of Material

Postby Don » Mon Dec 12, 2011 9:13 pm

Ok so I only had to make a small modification to the code and I left the old code in there but just commented it out. Try it out and let me know how it works:

Code: Select all
Sub Test_Multiple_Conditions()

Application.ScreenUpdating = False

Dim designators() As String

lrow = Range("A" & Rows.Count).End(xlUp).Row

For i = lrow To 2 Step -1


    designator = Cells(i, 1).Value
    desc = Cells(i, 2).Value
    qty = Cells(i, 3).Value
    Price = Cells(i, 4).Value
   
    designators = Split(designator, ",")

    'count commas
    commacount = Len(designator) - Len(Replace(designator, ",", ""))
   
    For r = 1 To commacount + 1
   
        'check for dash
        If InStr(designators(r - 1), "-") Then
            num1 = Mid(designators(r - 1), 2, 1)
            num2 = Right(designators(r - 1), 1)
            letter = Left(designators(r - 1), 1)
           
            For n = num1 To num2
           
                If CInt(n) = CInt(num1) Then
                    designators(r - 1) = letter & n
                Else
                    ReDim Preserve designators(0 To UBound(designators) + 1) As String
                    designators(UBound(designators)) = letter & n
                End If
               
            Next n
           
        End If
           
    Next r
           
       
    For s = LBound(designators) To UBound(designators)
        Cells(i, 1).Offset(1).EntireRow.Insert
        Cells(i, 1).EntireRow.Copy
        Cells(i, 1).Offset(1).PasteSpecial
        Cells(i, 1).Offset(1).Value = Trim(designators(s))
'        Cells(i, 1).Offset(1, 1).Value = desc
        Cells(i, 1).Offset(1, 2).Value = 1
'        Cells(i, 1).Offset(1, 3).Value = Price
    Next s
   
   
    Cells(i, 1).EntireRow.Delete

Next i

Application.ScreenUpdating = True

End Sub
  • 0

Don
Moderator
 
Posts: 733
Joined: Dec 4, 2011
Reputation: 2
Excel Version: 2010

Re: Normalizing data on a Bill of Material

Postby PSB » Mon Dec 12, 2011 10:33 pm

Hi Don,
PERFECT!!!! Thanks so much, I wish I could repay the favor. I'll work with this for a while and if I run into the C1-C5, C19 BOMs I'll see if it's possible to work this that configuration. Thanks again.
  • 0

PSB
Rookie
 
Posts: 16
Joined: Dec 9, 2011
Reputation: 0

Re: Normalizing data on a Bill of Material

Postby PSB » Wed Jan 04, 2012 4:19 pm

Hi Don,
Your code has been working perfectly and saved me a ton of time. I have attached an example that we talked about earlier where the reference designators are not list individually delimited by commas but instead grouped by a hyphen if they are sequential. In this spreadsheet I've included two possible scenarios.
1) the reference designator letter is listed twice within each hyphen (i.e. C11-C16, C19, C25-C29)
2) the reference designator letter is only listed once within each hyphen (i.e. C11-16, C19, C25-29)

Do you think you can modified your code to look for all conditions and the perform the task of breaking each out into it's own line? Thanks again, Pat.
  • 0

You do not have the required permissions to view the files attached to this post.
PSB
Rookie
 
Posts: 16
Joined: Dec 9, 2011
Reputation: 0

Previous

Return to Macros and VBA Questions

Who is online

Users browsing this forum: Google [Bot] and 44 guests