Any help on combining items for a quote form?

[seefactor]

Hello all,

As a long time reader, thanks for all the help - there is such a wealth of helpful knowledge here!

I am tasked with tweaking a quote form for electrical fixtures which include lamps (bulbs). On the top of the quote, I have the fixtures, fixture types, prices, etc. At the bottom, I bring down each fixture type into a section where the bulbs can be added.

For example:
The top fixture lines 1-4 might read:

Type A, Descrip, Quan, Catalog #, COGS($) & Sell ($)
Type B, Descrip, Quan, Catalog #, COGS($) & Sell ($)
Type C, Descrip, Quan, Catalog #, COGS($) & Sell ($)
Type D, Descrip, Quan, Catalog #, COGS($) & Sell ($)
...and so on

So the bottom lamp lines might read (quantities entered for example only):

Type A, "F32T8/835", Quan(10), COGS ($) & Sell ($)
Type B, "45wMR16/NFL", Quan(5), COGS ($) & Sell ($)
Type C, "F32T8/835", Quan(20), COGS ($) & Sell ($)
Type D, "90PAR38/FL", Quan(0), COGS ($) & Sell ($)
(whew...)

My desire is to consolidate the same lamps on one line (as in the case of Type A and C), append the Type names ("A & C") and associated $ values...like this...

"Type A & Type C", "F32T8/835", Total Quan(30), Total COGS ($) & Total Extended Sell ($)
"Type B", "45wMR16/NFL", Quan(5), COGS ($) & Sell ($)
and no entry for Type D lamps since Quan = 0 (this line needs to be hidden)

(I tried Subtotals but ended up with a lot of hidden lines being unhidden and lots of fixtures without lamps showing up (unwanted).)

Can this be done in Excel so that as quantities change, the lamps totals are dynamic? Let me know if additional info is needed.

There is one additional scenario that may occur where two Types use the same lamps but only one of them have lamps...in this case, only the Type with the lamps would need to be seen.

The reason I need to do this is when the purchasing people get the quote, they only need to enter 1 lamp + total quantity, not multiple line entries for the same lamp (which happens every time). Of course my quotes are more than four lines...some quotes are 40 lines!

I would be open to using Excel functions or VBA (or even pivot table?)

Thx for any help in advance,
Greg

 

[Bill Rockenbach]

Do you ever have a case were a fixture will accept more than one lamp type?

 

[seefactor]

Hi Bill,

No, 1 fixture -> 1 lamp type...there is the case where Exit and Emergency lighting already has lamps so no lamp line is needed or Track line entry which doesn't use lamps at all (the track heads use the lamps, which would be a separate line).

Thanks,
Greg

 

[seefactor]

Hi all,

Note: The way I get to the solution doesn't have to follow the flow I've outlined...for example, if there's a thought that we should enter the fixture and lamp together on the same row and then initiate a process at the end with a button/macro combination to farm the data entered onto a new, separate quote form to be printed, then I'm open to trying that. (Sorry for the run-on sentence!)

Thanks,
Greg

 

[Bill Rockenbach]

This code will summarize the Lamp Types. I don't know how your quote form is set up. Most likely this should go in a sheet code module. It may be more legible to illiminate all the "Type" text.

 Option Explicit
Sub combine()
    Dim i As Integer
    Dim j As Integer
    Dim k As Integer
    Dim LampArray
    Dim LampsDel()
    Dim LampsOut()
    Dim Quote1 As Integer
    Dim Quote2 As Integer
    Dim ChrLoc As Integer
    Dim CommaLoc As Integer
    Dim LampCode As String
    Dim OutPos As Integer
    Dim DelPos As Integer
    Dim DeletedItem As Boolean
    Dim TypeRange As Range
    '  Format of Type record
    '  Sample :  Type A, "F32T8/835", Quan(10), COGS ($) & Sell ($)
    '  Type xxxx, "aaaaaaaaa", Description
    '  xxxx, any length alpha/numeric Type code (must be a comma immideately folowing the Type code)
    '  "aaaaa" any length alpha/numeric Lamp number (must be enclosed in double quote marks
    Set TypeRange = Range("A1:A14")    ' Any range the Type lamps are in
    TypeRange.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
                   OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    LampArray = TypeRange
    ReDim LampsOut(1 To UBound(LampArray, 1), 1 To UBound(LampArray, 1))
    'ReDim LampsOut(UBound(LampArray))
    ReDim LampsDel(UBound(LampArray))
    For i = 1 To UBound(LampArray)
        'MsgBox "After i" & "   " & LampArray(i, 1)
        Quote1 = InStr(LampArray(i, 1), """")
        Quote2 = InStrRev(LampArray(i, 1), """")
        LampCode = Mid(LampArray(i, 1), Quote1, Quote2 - Quote1 + 1)
        DeletedItem = False
        For k = 1 To UBound(LampsDel)
            If LampArray(i, 1) = LampsDel(k) Then
                DeletedItem = True
                Exit For
            End If
        Next k
        If DeletedItem = False Then
            For j = i + 1 To UBound(LampArray)
                If InStr(LampArray(j, 1), LampCode) Then
                    DeletedItem = False
                    For k = 1 To UBound(LampsDel)
                        If LampArray(j, 1) = LampsDel(k) Then
                            DeletedItem = True
                            Exit For
                        End If
                    Next k
                    If DeletedItem = False Then
                        CommaLoc = InStr(LampArray(i, 1), ",")
                        ChrLoc = InStr(LampArray(j, 1), ",")
                        LampArray(i, 1) = Left(LampArray(i, 1), CommaLoc - 1) & " & " & _
                                          Left(LampArray(j, 1), ChrLoc - 1) & _
                                          Right(LampArray(i, 1), Len(LampArray(i, 1)) - CommaLoc + 1)
                        DelPos = DelPos + 1
                        LampsDel(DelPos) = LampArray(j, 1)
                    End If
                End If
            Next j
            OutPos = OutPos + 1
            LampsOut(OutPos, 1) = LampArray(i, 1)
        End If
    Next i
    TypeRange.Clear 'include this code if output is to override original Type code list
    Range("A1").Resize(UBound(LampsOut), 1).Value = LampsOut 'output can be directed to any sheet any location
End Sub<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
 <o:p></o:p>
 <o:p></o:p>
[RANGE=cls:xl2bb-100][XR][XH=cs:2]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][/XR][XR][XH]1[/XH][XD=h:l]Type A, "F32T8/835", Quan(10), COGS ($) & Sell ($)[/XD][/XR][XR][XH]2[/XH][XD=h:l]Type AB, "90PAR38/FL1", Quan(0), COGS ($) & Sell ($)[/XD][/XR][XR][XH]3[/XH][XD=h:l]Type ACbh, "90PAR38/FL1", Quan(0), COGS ($) & Sell ($)[/XD][/XR][XR][XH]4[/XH][XD=h:l]Type B, "45wMR16/NFL", Quan(5), COGS ($) & Sell ($)[/XD][/XR][XR][XH]5[/XH][XD=h:l]Type C, "F32T8/835", Quan(20), COGS ($) & Sell ($)[/XD][/XR][XR][XH]6[/XH][XD=h:l]Type D, "90PAR38/FL", Quan(0), COGS ($) & Sell ($)[/XD][/XR][XR][XH]7[/XH][XD=h:l]Type E, "F32T8/835", Quan(10), COGS ($) & Sell ($)[/XD][/XR][XR][XH]8[/XH][XD=h:l]Type F, "45wMR16/NFL", Quan(5), COGS ($) & Sell ($)[/XD][/XR][XR][XH]9[/XH][XD=h:l]Type G, "F32T8/835", Quan(20), COGS ($) & Sell ($)[/XD][/XR][XR][XH]10[/XH][XD=h:l]Type H, "90PAR38/FL", Quan(0), COGS ($) & Sell ($)[/XD][/XR][XR][XH]11[/XH][XD=h:l]Type I, "F32T8/835", Quan(10), COGS ($) & Sell ($)[/XD][/XR][XR][XH]12[/XH][XD=h:l]Type J, "45wMR16/NFL", Quan(5), COGS ($) & Sell ($)[/XD][/XR][XR][XH]13[/XH][XD=h:l]Type K, "F32T8/835", Quan(20), COGS ($) & Sell ($)[/XD][/XR][XR][XH]14[/XH][XD=h:l]Type L, "90PAR38/FL", Quan(0), COGS ($) & Sell ($)[/XD][/XR][XR][XH=cs:2][RANGE][XR][XD]Lamp List Before[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]
 <o:p></o:p>
 <o:p></o:p>
[RANGE=cls:xl2bb-100][XR][XH=cs:2]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][/XR][XR][XH]1[/XH][XD=h:l]Type A & Type C & Type E & Type G & Type I & Type K, "F32T8/835", Quan(10), COGS ($) & Sell ($)[/XD][/XR][XR][XH]2[/XH][XD=h:l]Type AB & Type ACbh, "90PAR38/FL1", Quan(0), COGS ($) & Sell ($)[/XD][/XR][XR][XH]3[/XH][XD=h:l]Type B & Type F & Type J, "45wMR16/NFL", Quan(5), COGS ($) & Sell ($)[/XD][/XR][XR][XH]4[/XH][XD=h:l]Type D & Type H & Type L, "90PAR38/FL", Quan(0), COGS ($) & Sell ($)[/XD][/XR][XR][XH=cs:2][RANGE][XR][XD]Lamp List After[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE]

<o></o>

 

[Bill Rockenbach]

Alternat output without all the "Type" text

Excel Workbook
	B
1	Type A, K, I, G, E, C, "F32T8/835", Quan(10), COGS ($) & Sell ($)
2	Type AB, ACbh, "90PAR38/FL1", Quan(0), COGS ($) & Sell ($)
3	Type B, J, F, "45wMR16/NFL", Quan(5), COGS ($) & Sell ($)
4	Type D, L, H, "90PAR38/FL", Quan(0), COGS ($) & Sell ($)
Sheet2

 

[seefactor]

Hi Bill,

Happy New Year!

Thank you for the code - I'm just coming back to the office and haven't yet had a chance to really look at what you've sent but did want to at least acknowledge that you've given me something to look at! I appreciate your time and input.

Best,
Greg

 

[Bill Rockenbach]

One way to improve the readablity of teh output would be to left justify the product codes and discriptions

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial, Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Type A, K, I, G, E, C, "F32T8/835", Quan(10), COGS ($) & Sell ($)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Type AB, ACbh, "90PAR38/FL1", Quan(0), COGS ($) & Sell ($)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Type B, J, F, "45wMR16/NFL", Quan(5), COGS ($) & Sell ($)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Type D, L, H, "90PAR38/FL", Quan(0), COGS ($) & Sell ($)</TD></TR></table> <table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial, Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Type A, K, I, G, E, C, "F32T8/835", Quan(10), COGS ($) & Sell ($)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Type AB, ACbh, "90PAR38/FL1", Quan(0), COGS ($) & Sell ($)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Type B, J, F, "45wMR16/NFL", Quan(5), COGS ($) & Sell ($)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Type D, L, H, "90PAR38/FL", Quan(0), COGS ($) & Sell ($)</TD></TR></table> <table style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial, Arial; FONT-SIZE: 10pt" border=1 cellSpacing=0 cellPadding=0><TR style="HEIGHT: 17px"><TD>Type A, K, I, G, E, C, "F32T8/835", Quan(10), COGS ($) & Sell ($)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>Type AB, ACbh, "90PAR38/FL1", Quan(0), COGS ($) & Sell ($)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD>Type B, J, F, "45wMR16/NFL", Quan(5), COGS ($) & Sell ($)</TD></TR><TR style="HEIGHT: 17px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Type D, L, H, "90PAR38/FL", Quan(0), COGS ($) & Sell ($)</TD></TR></TABLE>
Maybe one of these patterns

Excel Workbook
	A
1	Type A, K, I, G, E, C, "F32T8/835", Quan(10), COGS ($) & Sell ($)
2	Type AB, ACbh, "90PAR38/FL1", Quan(0), COGS ($) & Sell ($)
3	"45wMR16/NFL", Quan(5), COGS ($) & Sell ($) Type B, J, F,
4	Type D, L, H, "90PAR38/FL", Quan(0), COGS ($) & Sell ($)
5
6
7	"F32T8/835", Quan(10), COGS ($) & Sell ($) Type A, K, I, G, E, C,
8	"90PAR38/FL1", Quan(0), COGS ($) & Sell ($) Type AB, ACbh,
9	"45wMR16/NFL", Quan(5), COGS ($) & Sell ($) Type B, J, F,
10	"90PAR38/FL", Quan(0), COGS ($) & Sell ($) Type D, L, H,
Sheet1

 

[seefactor]

Hi Bill,

I was a little too literal in my example - maybe it will be easier...each of the items (Type, lamp, $, etc.) are in their own column (not feasible to put in commas or quotes around items). Would you mind taking a look to see if that simplifies the code (instead of having to look for specific characters, counting spaces, etc.)?

Thank you,
Greg