Complex Script Question

H

Hyperlite

New Member
Joined
Jun 27, 2012
Messages
10
Hi everyone,

I have somewhat of an issue figuring out a method to script this in excel. I have an excel document that contains a couple bits of information, and I need to sort it with logic in a specific manner. A single cell contains numerous values seperated by a ", " (comma followed by space). The output format must also stay using a single cell for multiple values for the script to be useful.

I want to organize the info in the Ref Des column of Chart 1 according to the data on Chart 2. It will also then tally up the total number of values in each of those formatted cells and display the total number of individual values in that cell.

Chart 1:
Known Data on Sheet 1 of Excell Document 1 (logic not applied to far 4 columns)[TABLE="class: grid, width: 700"]
<tbody>[TR]
[TD="align: center"]Line Item #
[/TD]
[TD="align: center"]Ref Des
[/TD]
[TD="align: center"]Ref Des T
[/TD]
[TD="align: center"]QTY on T
[/TD]
[TD="align: center"]Ref Des B
[/TD]
[TD="align: center"]QTY on B
[/TD]
[TD="align: center"]Total QTY
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]R100, R102, R104, R105
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]U1, U2, U13
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]3
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]C3, C105, C225, L24
[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"]4
[/TD]
[/TR]
</tbody>[/TABLE]

Chart 2:
Known Data on Sheet 2 of Excell Document 1[TABLE="class: grid, width: 150"]
<tbody>[TR]
[TD="align: center"]Ref Des
[/TD]
[TD="align: center"]T or B
[/TD]
[/TR]
[TR]
[TD="align: center"]R100
[/TD]
[TD="align: center"]TOP
[/TD]
[/TR]
[TR]
[TD="align: center"]R102
[/TD]
[TD="align: center"]BOTTOM
[/TD]
[/TR]
[TR]
[TD="align: center"]R104
[/TD]
[TD="align: center"]BOTTOM
[/TD]
[/TR]
[TR]
[TD="align: center"]R105
[/TD]
[TD="align: center"]BOTTOM
[/TD]
[/TR]
[TR]
[TD="align: center"]U1
[/TD]
[TD="align: center"]TOP
[/TD]
[/TR]
[TR]
[TD="align: center"]U2
[/TD]
[TD="align: center"]TOP
[/TD]
[/TR]
[TR]
[TD="align: center"]U13
[/TD]
[TD="align: center"]BOTTOM
[/TD]
[/TR]
[TR]
[TD="align: center"]C3
[/TD]
[TD="align: center"]TOP
[/TD]
[/TR]
[TR]
[TD="align: center"]C105
[/TD]
[TD="align: center"]TOP
[/TD]
[/TR]
[TR]
[TD="align: center"]C225
[/TD]
[TD="align: center"]BOTTOM
[/TD]
[/TR]
[TR]
[TD="align: center"]L24
[/TD]
[TD="align: center"]BOTTOM
[/TD]
[/TR]
</tbody>[/TABLE]

Chart 3:
Desired Output Format from using script or logic on Sheet 1/Sheet 2 next to known data from above. The italicized headings are for reference. The italicized and bolded values are the desired outputs I should get from a successful script.[TABLE="class: grid, width: 1100"]
<tbody>[TR]
[TD="align: center"]Known Above
[/TD]
[TD="align: center"]Known Above
[/TD]
[TD="align: center"]Applied Logic Column
[/TD]
[TD="align: center"]Applied Logic Column
[/TD]
[TD="align: center"]Applied Logic Column
[/TD]
[TD="align: center"]Applied Logic Column
[/TD]
[TD="align: center"]Known Above
[/TD]
[/TR]
[TR]
[TD="align: center"]Line Item #
[/TD]
[TD="align: center"]Ref Des
[/TD]
[TD="align: center"]Ref Des T
[/TD]
[TD="align: center"]QTY on T
[/TD]
[TD="align: center"]Ref Des B
[/TD]
[TD="align: center"]QTY on B
[/TD]
[TD="align: center"]Total QTY
[/TD]
[/TR]
[TR]
[TD="align: center"]1
[/TD]
[TD="align: center"]R100, R102, R104, R105
[/TD]
[TD="align: center"]R100
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]R102, R104, R105
[/TD]
[TD="align: center"]3
[/TD]
[TD="align: center"]4
[/TD]
[/TR]
[TR]
[TD="align: center"]2
[/TD]
[TD="align: center"]U1, U2, U13
[/TD]
[TD="align: center"]U1, U2
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]U13
[/TD]
[TD="align: center"]1
[/TD]
[TD="align: center"]3
[/TD]
[/TR]
[TR]
[TD="align: center"]3
[/TD]
[TD="align: center"]C3, C105, C225, L24
[/TD]
[TD="align: center"]C3, C105
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]C225, L24
[/TD]
[TD="align: center"]2
[/TD]
[TD="align: center"]4
[/TD]
[/TR]
</tbody>[/TABLE]


We currently have a script that can do this, but it has some limitations and is not imbedded inside excel. I want to be able to undersatnd how the script works as the values are not always seperated by just a ", " (comma followed by space) but also sometimes just a " " (space) or "," (comma with no space) etc. (documents do not mix seperation styles, they just use one or the other). The excel documents we are doing this to are much more complex than what I am showing here as this is a more simplistic view for asking the question. (The Ref Des cells can have 100's of values seperated by ","/" "/", " not just 3 or 4, and there can be 100's of line items, not just 3 or 4). However, the complexity of the data should not matter as the basic functionality of the script would be the same in my opinion.

If this is not possible in excel, please suggest or point me in the direction of some other methods I could look into to acheive this. Currently, seperating this by hand is extremely time consuming :-( I thought about using VBScript to do this, but wanted to see if it was possible inside Excel first.

I tried to be as thorough as possible in explaining what I want to achieve, but as an engineer I can sometimes leave details out that might be needed to solve the problem. I will try to keep tabs on this thread from my phone to answer any questions that may arise.

Thank you.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Sort by date Sort by votes
Hi, an welcome to the forum.

Here is a VBA solution to your problem.

I have created two object variables,
One to loop through column B of sheet1, named rngSheet1;
The other for sheet2, named wsRef.

Code: 
   [COLOR=darkblue]Set[/COLOR] rngSheet1 = Sheets("Sheet1").Range("B2")
   [COLOR=darkblue]Set[/COLOR] wsRef = Sheets("Sheet2")

We loop through column B and split the individual values into an array, based on the comma separator, edit this to meet your needs.
Code: 
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngSheet1 = ""
      [COLOR=green]'spilit into the array on comma[/COLOR]
      arrRef = Split(rngSheet1.Value, [COLOR=#ff0000]","[/COLOR])

We then loop through the array and determine if each value corresponds to "TOP" or "BOTTOM". I have written a separate Function for this process named, FindRef(). The function uses the Find() function to search Sheet2 column(1) for the ref and returns the value in the adjacent column.

Code: 
      [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](arrRef) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](arrRef)
         strTemp = FindRef(wsRef, Trim(arrRef(i)))

The function returns TOP or BOTTOM to the strTemp variable. We test this using a Select Case statement. We also determine the column offset here for output.

For example if strTemp="TOP"

Code: 
Select Case UCase(strTemp)
            Case "TOP"
               If rngSheet1.Offset(, 1).Value = "" Then
                  rngSheet1.Offset(, 1).Value = arrRef(i)
               Else
                  rngSheet1.Offset(, 1).Value = rngSheet1.Offset(, 1).Value & ", " & arrRef(i)
               End If
               rngSheet1.Offset(, 2).Value = rngSheet1.Offset(, 2).Value + 1


The full code is below.
To test:
Create a new Excel file and place your sample data in Sheet1 and Sheet2.
Press ALt+F11 to open the VBA Editor window.
Double click the ThisWorkbook module in the Project Window on the left hand side.
Copy and paste the code.
Press F5 to run or F8 to step through the code(recommended for a better understanding of how the code works.).

Code: 
[COLOR=darkblue]Option[/COLOR] [COLOR=darkblue]Explicit[/COLOR]


[COLOR=darkblue]Sub[/COLOR] Main()
   [COLOR=darkblue]Dim[/COLOR] rngSheet1 [COLOR=darkblue]As[/COLOR] Range
   [COLOR=darkblue]Dim[/COLOR] wsRef [COLOR=darkblue]As[/COLOR] Worksheet
   [COLOR=darkblue]Dim[/COLOR] arrRef [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Variant[/COLOR]      [COLOR=green]'array of references[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] i [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Long[/COLOR]              [COLOR=green]'loop variable[/COLOR]
   [COLOR=darkblue]Dim[/COLOR] strTemp [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]
   
   [COLOR=darkblue]Set[/COLOR] rngSheet1 = Sheets("Sheet1").Range("B2")
   [COLOR=darkblue]Set[/COLOR] wsRef = Sheets("Sheet2")
   
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] errExit
   
   [COLOR=darkblue]Do[/COLOR] [COLOR=darkblue]Until[/COLOR] rngSheet1 = ""
      [COLOR=green]'spilit into the array on comma[/COLOR]
      arrRef = Split(rngSheet1.Value, ",")
      
      [COLOR=darkblue]For[/COLOR] i = [COLOR=darkblue]LBound[/COLOR](arrRef) [COLOR=darkblue]To[/COLOR] [COLOR=darkblue]UBound[/COLOR](arrRef)
         strTemp = FindRef(wsRef, Trim(arrRef(i)))
         
         [COLOR=green]'output[/COLOR]
         [COLOR=darkblue]Select[/COLOR] [COLOR=darkblue]Case[/COLOR] U[COLOR=darkblue]Case[/COLOR](strTemp)
            [COLOR=darkblue]Case[/COLOR] "TOP"
               [COLOR=darkblue]If[/COLOR] rngSheet1.Offset(, 1).Value = "" [COLOR=darkblue]Then[/COLOR]
                  rngSheet1.Offset(, 1).Value = arrRef(i)
               [COLOR=darkblue]Else[/COLOR]
                  rngSheet1.Offset(, 1).Value = rngSheet1.Offset(, 1).Value & ", " & arrRef(i)
               [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
               rngSheet1.Offset(, 2).Value = rngSheet1.Offset(, 2).Value + 1
               
               
            [COLOR=darkblue]Case[/COLOR] "BOTTOM"
               [COLOR=darkblue]If[/COLOR] rngSheet1.Offset(, 3).Value = "" [COLOR=darkblue]Then[/COLOR]
                  rngSheet1.Offset(, 3).Value = arrRef(i)
               [COLOR=darkblue]Else[/COLOR]
                  rngSheet1.Offset(, 3).Value = rngSheet1.Offset(, 3).Value & ", " & arrRef(i)
               [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
               rngSheet1.Offset(, 4).Value = rngSheet1.Offset(, 4).Value + 1
            
            
            Case [COLOR=darkblue]Else[/COLOR]
               [COLOR=green]'not found[/COLOR]
               'code for ref not found goes here
         [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Select[/COLOR]
         
         [COLOR=green]'calculate total[/COLOR]
         rngSheet1.Offset(, 5).Value = _
            rngSheet1.Offset(, 2).Value + rngSheet1.Offset(, 4).Value
      [COLOR=darkblue]Next[/COLOR] i
      
      [COLOR=green]'next row[/COLOR]
      [COLOR=darkblue]Set[/COLOR] rngSheet1 = rngSheet1.Offset(1, 0)
   [COLOR=darkblue]Loop[/COLOR]
   
errExit:
   [COLOR=green]'tidy up and release memory[/COLOR]
   [COLOR=darkblue]Set[/COLOR] rngSheet1 = [COLOR=darkblue]Nothing[/COLOR]
   [COLOR=darkblue]Set[/COLOR] wsRef = [COLOR=darkblue]Nothing[/COLOR]
   
[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]




[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Function[/COLOR] FindRef([COLOR=darkblue]ByVal[/COLOR] ws [COLOR=darkblue]As[/COLOR] Worksheet, _
                         [COLOR=darkblue]ByVal[/COLOR] ref [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]String[/COLOR]) [COLOR=darkblue]As[/COLOR] String
   [COLOR=darkblue]Dim[/COLOR] rngFound [COLOR=darkblue]As[/COLOR] Range
   
   [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]Resume[/COLOR] [COLOR=darkblue]Next[/COLOR]
      [COLOR=darkblue]With[/COLOR] ws
         [COLOR=darkblue]Set[/COLOR] rngFound = .Columns(1).Find(What:=ref, _
                        After:=.Cells(1, 1), _
                        LookIn:=xlValues, _
                        LookAt:=xlPart, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False, _
                        SearchFormat:=False)
         [COLOR=darkblue]On[/COLOR] [COLOR=darkblue]Error[/COLOR] [COLOR=darkblue]GoTo[/COLOR] 0
      [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]With[/COLOR]
      
   [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Not[/COLOR] rngFound [COLOR=darkblue]Is[/COLOR] [COLOR=darkblue]Nothing[/COLOR] [COLOR=darkblue]Then[/COLOR]
      FindRef = rngFound.Offset(, 1).Value
   [COLOR=darkblue]Else[/COLOR]
      FindRef = ""
   [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Function[/COLOR]

Bertie
 
Upvote 0
Thanks a bunch for the quick reply Bertie. I will play around with this tomorrow at work and post any questions I might have.
 
Upvote 0
You must log in or register to reply here.

Similar threads

mehidy1437
  • Question Question
Final code required expert opinion
Replies
8
Views
906
mehidy1437
mehidy1437
M
  • Question Question
automate the sheet
Replies
0
Views
333
mwvirk
M
D
  • Question Question
86 Seconds to run my bit of Code to delete blank rows ! Would like it to run faster, learning
Replies
4
Views
434
Ottsel
Ottsel
H
  • Question Question
Need Help Merging 3 VBA Scripts into 1
Replies
5
Views
325
Hyperlite
H
K
  • Question Question
Check my played combinations with results database
Replies
5
Views
885
Kishan
K

Forum statistics

Threads
1,223,176
Messages
6,170,542
Members
452,336
Latest member
boekl007

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top