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.

Normalizing data on a Bill of Material

Postby PSB » Fri Dec 09, 2011 5:39 pm

I am new to this site, I just signed up today. If I am not following correct protocol please let me know. I have played with VB code in Access and have done a very little in Excel. My question will definitely require code. I'd attach an example spreadsheet but I'm not sure how. Thanks in advance for any and all assistance. I'll start with a simple example as it will then be easier to frame my question.

reference designator description quantity price
Row 1: c1,c2,c3,c8,c11 capacitor 5 $0.12
Row 2: r5,r6,r10 resistor 3 $0.40

I need to break out each reference designator into it's own line. Therefore, the code needs to look at the quantity cell, subtract 1 and insert that many lines directly below the line being evaluated. Next it needs to copy the entire contents of that line into all the blank lines. It then needs to parse the reference designators so each one is on a unique line. Lastly, it should change the quantity cell to 1 for each item. I have no clue if this is the proper sequence, I'm just logically thinking of what needs to occur. The end results of this example is as follows:

reference designator description quantity price
Row 1: c1 capacitor 1 $0.12
Row 2: c2 capacitor 1 $0.12
Row 3: c3 capacitor 1 $0.12
Row 4: c8 capacitor 1 $0.12
Row 5: c11 capacitor 1 $0.12
Row 6: r5 resistor 1 $0.40
Row 7: r6 resistor 1 $0.40
Row 8: r10 resistor 1 $0.40

In this example the delimiter in the reference designator field is always a comma and it separates one reference designator. I'd be thrilled if I can get code that will work with that format. I know that I may see one additional format in the reference designator field. That would be a combination of hyphens and commas. I may see something like: r1-r4,r9 which = r1,r2,r3,r4,r9. I have no idea how difficult it would be to write code to handle both scenarios (I'm sure I will see cases where both configurations appear in one spreadsheet - see below)

reference designator description quantity price
Row 1: c1,c5 capacitor 2 $0.12
Row 2: r1-r4, r9 resister 5 $0.40

Again, thanks in advance for you help.
  • 0

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

Re: Normalizing data on a Bill of Material

Postby Don » Fri Dec 09, 2011 6:35 pm

*Moved to the programming forum since this deals with VBA

Welcome to the forum!

If you need to attach a file, just go to make a post and look under the post window, it will say "Upload attachment" on one of the tabs and you can upload files from there.

I made a quick macro to take care of the first scenario. It might not do everything you need, but I'll come back to it tomorrow for the second scenario if you still need it.

Code: Select all
Sub Test()


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
        Cells(i, 1).Offset(1).EntireRow.Insert
        Cells(i, 1).Offset(1).Value = designators(r - 1)
        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 r
   

Next i


End Sub


This assumes that your data starts in column A and that there is a header row in row 1. This should get you most of the way there! :)
  • 0

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

Re: Normalizing data on a Bill of Material

Postby PSB » Fri Dec 09, 2011 8:38 pm

Hi Don,
Thanks so much for such an amazingly quick response! I won't be able to test until later this weekend. Thanks again, Pat
  • 0

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

Re: Normalizing data on a Bill of Material

Postby PSB » Sun Dec 11, 2011 7:49 pm

Hi Don,
Thanks so much, it worked perfectly and did everything I asked for. I realized I forgot two important actions.
1) Once a line is parsed I need to original line erased. This eliminates duplicate information.
2) How do you remove any leading spaces when the reference designators are parsed?

The reason this is imperative is I will use this code on two Bills of Materials of different revisions. Once they are normalized I then use V-Lookup to find the deltas between the two BOMs. This is a manual step and relies on the reference designators being exactly the same (i.e. "C1" Vs " C1"). Is this something you can help me with?

As far as the second type of Bill of Material with reference designators of different configurations (i.e. C1-C4, C9, C15), would that be an easy modification to this code? Again, thanks so much, you can't believe how much work you are going to save me. Thanks, Pat
  • 0

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

Re: Normalizing data on a Bill of Material

Postby Don » Sun Dec 11, 2011 9:13 pm

For the trim issue, that's a pretty easy fix in the code (trim() function just like in Excel) and I will include that in the final macro. I'll look over the second issue here shortly.
  • 0

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

Re: Normalizing data on a Bill of Material

Postby Don » Sun Dec 11, 2011 9:52 pm

Alright, try out this baby:

Code: Select all
Sub Test_Multiple_Conditions()

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).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


End Sub


Made a few assumptions here to make writing the macro quicker, so, let me know how it works.
  • 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 8:16 am

Hi Don,
It removed leading spaces and erased the original lines, perfect. However, it didn't copy the entire line to all parsed reference designators. For each parsed line I need all the data from the original line (all columns). Thanks
  • 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:46 am

In the data that I used for testing it worked just fine. If can you include a sample file of the data you are using, I could test the macro on the exact data you are using to ensure there are no differences.

Also, will the ranges for the numbers ever go above 10? So, could it ever be something like: r4-r22?
  • 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 11:04 am

Hi Don,
Attached is a sample file. It looks a little different then the typed example I provided. Each customer's spreadsheet will be a little different. I'll have to make sure that each one as the reference designators in column A and the quantity in column C to match your code (I've done that here). Yes, there could be well over 10 reference designators - this example has that situation. Thanks, 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

Re: Normalizing data on a Bill of Material

Postby Don » Mon Dec 12, 2011 5:41 pm

There are many more columns in there now lol. I'll try to get you an example later tonight.
  • 0

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

Next

Return to Macros and VBA Questions

Who is online

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