Mindblowing Excel Problem - Data Manipulation

Hi Experts,

I have an urgent need to simplify a large dataset I am working on.

As you can see in the attachment (tab CDXD1Y23B-Panel), the layout of the worksheets is pretty complicated.
I need to strip hundreds of worksheets in a large workbook into a simple csv format as seen in the attached example (tab Combined Output CSV).

This started with thousands of files in xls format, and I figured out how to roll all of the worksheets into a giant workbook, but have no clue whatsoever on how to convert this data.

Any help would be most greatly appreciated.

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I started to solve this when I realized that the joined cells are actually merged both vertically and horizontally.  Code won't be as simple :-(
Marv1nAuthor Commented:
Yeah, I've tried several approaches but I'm not making any progress.  Thanks for taking a shot.
[ fanpages ]IT Services ConsultantCommented:
Are all the (cell [AJ5]) "Panel" worksheets in exactly the same format; i.e. all field headings (row 2 of [Combined Output CSV]) in the same cells, & all field contents (row 3 in [Combined Output CSV]) in the same cells, of each individual worksheet?

...or are all the layouts different?

Hence, are there eleven Load/Trip Setting/Model combinations, & the same set of "headers" ("NAME:", "VOLTAGE:", "PHASE/WIRE:", "AMPERAGE:". "AIC:", "FED FROM:", "THROUGH:", & "MANUFACTURER:") per "Panel" worksheet?

Could you confirm, or post a few examples worksheets rather than just the [CDXD1Y23B-Panel] worksheet?

Also, are all the "Panel" worksheets in one workbook, or do you have many workbooks with a single worksheet?

Thank for your clarification.
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

Marv1nAuthor Commented:

Thanks much for your help!

All of the panel sheets have the information in the exact same spot. The output file is something I mocked up to show what I'd need to have this laid out in for input into another process I have automated.

The layout should be consistent across all of the worksheets.

All of the panel worksheets are rolled up into 1 workbook (ie. for the D1X building, there are 543 worksheets in the workbook). I also have these as single workbooks containing one worksheet each, if that helps at all.

Thanks again for your help!
[ fanpages ]IT Services ConsultantCommented:

If all the data required is in exactly the same cell location on each worksheet, I don't think it would be too much trouble to run through every worksheet & extract the text values from the fixed locations.

(Unless aikimark hit an issue I have not foreseen).

Would you be able to supply a couple more example worksheets so I have some test data ready for when I have some code running as intended?
Marv1nAuthor Commented:

Here you go - please let me know if you need more!

[ fanpages ]IT Services ConsultantCommented:
OK, will do.

Just writing the code now.

I'll be back when I have something demonstrable.
[ fanpages ]IT Services ConsultantCommented:
'tis going well.  Will be a few more minutes :)

PS. The following columns within the [Combined Output CSV] worksheet of your first attachment above...
"Trip Setting5"
"Trip Setting6"

...all state "Space", but the corresponding cells within the [CDXD1Y23B-Panel] worksheet are just blank (<empty>).

Do you want to see the blank cell, or replace with "Space"?
Marv1nAuthor Commented:
Either is good with me.

I put SPACE in there because it's basically a hole in the panel ;)
[ fanpages ]IT Services ConsultantCommented:
OK.  Also, you only have details up to...

"Load11" / "Trip Setting11" / "Model11"

My code goes that far, but I see the "Panel" worksheets go up to 19.

Shall I post what I have, & you can add the additional 8, if required?

(You will see from how I have written the code that it will be easy to do this)
Marv1nAuthor Commented:
So, there are two slices at this data that I'm going to take with your amazing assistance ;)

One is the complete extraction of all panel contents (which per your comment above, does extend well past 11).

The second is to limit the returned data based on some criteria - ie. only show panel sections that contain a J-frame (example: JGA36250U43X      ) component, etc.

It'd be awesome if you extended it to 19, but I'm not going to push my luck since I'm already EXTREMELY grateful for the help you're providing.      

Thanks very much for your help!
[ fanpages ]IT Services ConsultantCommented:
Another 8 won't be a huge amount of time.

I'll be back in 5 minutes :)
[ fanpages ]IT Services ConsultantCommented:
Please find attached a workbook that contains the four sample "Panel" worksheets, the sample output worksheet (renamed to [Sample Combined Output CSV]), & a "blank" [Combined Output CSV] output worksheet that will record the collation of details of the individual worksheets (that have a name with a suffix of "-Panel").

The code used is as follows...

Option Explicit

Private lngErr_Number                                   As Long
Private strErr_Description                              As String
Public Sub Q_28714125()

' -------------------------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/questions/28714125/Mindblowing-Excel-Problem-Data-Manipulation.html ]
' Question Channel: Experts Exchange > Questions > Mindblowing Excel Problem - Data Manipulation
' Topic Area:       [ http://www.experts-exchange.com/topics/ms-excel/ ]
' ID:               Q_28714125
' Question Title:   Mindblowing Excel Problem - Data Manipulation
' Question Dated:   2015-09-15 08:44 PM
' Question Asker:   Marv1n
' Asker Profile:    [ http://www.experts-exchange.com/members/Marv1n.html ]
' Solution posted:  16 September 2015 by fanpages
' Expert Profile:   [ http://www.experts-exchange.com/members/fanpages.html ] | [ http://www.experts-exchange.com/M_258171.html ]
' -------------------------------------------------------------------------------------------------------------------------------
' Copyright:        (c) 2015 Clearlogic Concepts (UK) Limited                                            [ http://NigelLee.info ]
' Licensed for use by Experts Exchange members under Experts Exchange Terms-of-Use, provided the copyright statement is retained.
' -------------------------------------------------------------------------------------------------------------------------------

' --------------------------------------------------
' Column/Heading            Panel worksheet Location
' --------------------------------------------------
' [A]   Building            [D2]
' [B]   Name                [AJ5]
' [C]   Voltage             [AJ6]
' [D]   Phase/Wire          [AJ7]
' [E]   Amperage            [AJ8]
' [F]   AIC                 [AJ9]
' [G]   Fed From            [AJ10]
' [H]   Through             [AJ11]
' [I]   Manufacturer        [AJ12]
' [J]   Load                [T17]
' [K]   Trip Setting        [T19]
' [L]   Model               [T21]
' [M]   Load2               [T29]
' [N]   Trip Setting2       [T31]
' [O]   Model2              [T33]
' [P]   Load3               [T41]
' [Q]   Trip Setting3       [T43]
' [R]   Model3              [T45]
' [S]   Load4               [T53]
' [T]   Trip Setting4       [T55]
' [U]   Model4              [T57]
' [V]   Load5               [T65]
' [W]   Trip Setting5       [T67]
' [X]   Model5              [T69]
' [Y]   Load6               [T77]
' [Z]   Trip Setting6       [T79]
' [AA]  Model6              [T81]
' [AB]  Load7               [T89]
' [AC]  Trip Setting7       [T91]
' [AD]  Model7              [T93]
' [AE]  Load8               [T101]
' [AF]  Trip Setting8       [T103]
' [AG]  Model8              [T105]
' [AH]  Load9               [AS15]
' [AI]  Trip Setting9       [AS17]
' [AJ]  Model9              [AS19]
' [AK]  Load10              [AS24]
' [AL]  Trip Setting10      [AS26]
' [AM]  Model10             [AS28]
' [AN]  Load11              [AS33]
' [AO]  Trip Setting11      [AS35]
' [AP]  Model11             [AS37]
' [AQ]  Load12              [AS42]
' [AR]  Trip Setting12      [AS44]
' [AS]  Model12             [AS46]
' [AT]  Load13              [AS51]
' [AU]  Trip Setting13      [AS53]
' [AV]  Model13             [AS55]
' [AV]  Load14              [AS60]
' [AW]  Trip Setting14      [AS62]
' [AY]  Model14             [AS64]
' [AZ]  Load15              [AS69]
' [BA]  Trip Setting15      [AS71]
' [BB]  Model15             [AS73]
' [BC]  Load16              [AS78]
' [BD]  Trip Setting16      [AS80]
' [BE]  Model16             [AS82]
' [BF]  Load17              [AS87]
' [BG]  Trip Setting17      [AS89]
' [BH]  Model17             [AS91]
' [BI]  Load18              [AS96]
' [BJ]  Trip Setting18      [AS98]
' [BK]  Model18             [AS100]
' [BL]  Load19              [AS105]
' [BM]  Trip Setting19      [AS107]
' [BN]  Model19             [AS109]
' --------------------------------------------------

  Dim objWorksheet                                      As Worksheet
  Dim objWorksheet_Output                               As Worksheet
  Dim lngRow                                            As Long
  Dim strPanel                                          As String
  Dim vntArray                                          As Variant
  On Error GoTo Err_Q_28714125
  Set objWorksheet_Output = ThisWorkbook.Worksheets("Combined Output CSV")
  lngRow = objWorksheet_Output.Cells(objWorksheet_Output.Rows.Count, "A").End(xlUp).Row
  If lngRow < 2& Then
     lngRow = 2&
  End If ' If lngRow < 2& Then
  For Each objWorksheet In ThisWorkbook.Worksheets
      strPanel = objWorksheet.[AJ5]
      Select Case (False)
          Case (Right$(objWorksheet.Name, 6) = "-Panel")
          Case (Left$(objWorksheet.Name, Len(objWorksheet.Name) - 6) = strPanel)
          Case Else
              vntArray = Array(objWorksheet.[D2], _
                               objWorksheet.[AJ5], objWorksheet.[AJ6].Value, objWorksheet.[AJ7], _
                               objWorksheet.[AJ8].Value, objWorksheet.[AJ9], objWorksheet.[AJ10], _
                               objWorksheet.[AJ11], objWorksheet.[AJ12], _
                               objWorksheet.[T17], objWorksheet.[T19], objWorksheet.[T21], _
                               objWorksheet.[T29], objWorksheet.[T31], objWorksheet.[T33], _
                               objWorksheet.[T41], objWorksheet.[T43], objWorksheet.[T45], _
                               objWorksheet.[T53], objWorksheet.[T55], objWorksheet.[T57], _
                               objWorksheet.[T65], objWorksheet.[T67], objWorksheet.[T69], _
                               objWorksheet.[T77], objWorksheet.[T79], objWorksheet.[T81], _
                               objWorksheet.[T89], objWorksheet.[T91], objWorksheet.[T93], _
                               objWorksheet.[T101], objWorksheet.[T103], objWorksheet.[T105], _
                               objWorksheet.[AS15], objWorksheet.[AS17], objWorksheet.[AS19], _
                               objWorksheet.[AS24], objWorksheet.[AS26], objWorksheet.[AS28], _
                               objWorksheet.[AS33], objWorksheet.[AS35], objWorksheet.[AS37], _
                               objWorksheet.[AS42], objWorksheet.[AS44], objWorksheet.[AS46], _
                               objWorksheet.[AS51], objWorksheet.[AS53], objWorksheet.[AS55], _
                               objWorksheet.[AS60], objWorksheet.[AS62], objWorksheet.[AS64], _
                               objWorksheet.[AS69], objWorksheet.[AS71], objWorksheet.[AS73], _
                               objWorksheet.[AS78], objWorksheet.[AS80], objWorksheet.[AS82], _
                               objWorksheet.[AS87], objWorksheet.[AS89], objWorksheet.[AS91], _
                               objWorksheet.[AS96], objWorksheet.[AS98], objWorksheet.[AS100], _
                               objWorksheet.[AS105], objWorksheet.[AS107], objWorksheet.[AS109])

              objWorksheet_Output.Range(objWorksheet_Output.Cells(lngRow, 1), _
                                        objWorksheet_Output.Cells(lngRow, 1).Offset(0, UBound(vntArray))) = vntArray
              If lngRow = objWorksheet_Output.Rows.Count Then
                 Exit For
                 lngRow = lngRow + 1&
              End If 'If lngRow = objWorksheet_Output.Rows.Count Then
      End Select ' Select Case (False)
      Set vntArray = Nothing
  Next objWorksheet ' For Each objWorksheet In ThisWorkbook.Worksheets

  On Error Resume Next
  Set vntArray = Nothing
  Set objWorksheet = Nothing
  If Not (objWorksheet_Output Is Nothing) Then
     Set objWorksheet_Output = Nothing
  End If ' If Not (objWorksheet_Output Is Nothing) Then
  Exit Sub

  lngErr_Number = Err.Number
  strErr_Description = Err.Description
  On Error Resume Next
  MsgBox "Error #" & CStr(lngErr_Number) & _
         vbCrLf & vbLf & _
         strErr_Description, _
         vbExclamation Or vbOKOnly, _

  Resume Exit_Q_28714125
End Sub

Open in new window

If you are planning to re-run the code after the first time, please remember to remove all the rows (except row 1; the column headings) from the [Combined Output CSV] worksheet, as subsequent executions of the code will just append additional rows of data (rather than deleting/overwriting any existing data).

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Marv1nAuthor Commented:

Thanks man  - I'm going to take this for a spin, I'll let you know how I make out.

Message me directly if I can ever help you with anything, or if you want a tour guide if you're ever in Nashville, TN.

Thanks again!
[ fanpages ]IT Services ConsultantCommented:
:) You're very welcome.

It's 00:19 on the UK now.  I'll be around for a little while longer, but if you do have any issues, simply post another comment, & I will pick this up later this morning.

Another "Expert" may be able to assist further in the meantime, in any respect.

Oh... I almost forgot...

The code checks the prefix of the individual "Panel" worksheets to verify that the "Panel" recorded in cell [AJ5] matches the worksheet (without the "-Panel" suffix), just as a verification that the data matches, or is as intended.

If you find this check too restrictive, simply comment out (prefix with an apostrophe), or delete, this single line (123 in the listing above):

          Case (Left$(objWorksheet.Name, Len(objWorksheet.Name) - 6) = strPanel)
Marv1nAuthor Commented:
Not only did Fanpages absolutely dive into this with an enthusiasm I've rarely seen, he was posting updates on what he was doing throughout the process!  Experts Exchange needs to add a "donate" button to the site so I can make my appreciation more directly felt when experts like this go above and beyond.

Truly a class act, and absolutely deserving of the "Expert" moniker.

Thanks again!!!
[ fanpages ]IT Services ConsultantCommented:
Awww shucks!  Stop it now :)

Perhaps I should follow the lead of another long-standing 'Expert' & add PayPal details to my Profile! ;)

Seriously, though, as I have said a few times in the past couple of weeks, I do enjoy getting stuck in to questions that are not the usual run-of-the-mill queries.

I tend to leave the 'usual' questions to the relatively new "Experts" so they can get some confidence (& build-up their profile).

Thank you for an out-of-the-ordinary question.
I had a meetup to attend this evening, so my work on this problem was interrupted.  Even though this problem has been solved, I thought I'd post my work.
Note: The output is going to Sheet1, which I added for development/testing purposes.
Sub Q_28714125()
    Dim rngSrc As Range
    Dim rngTgt As Range
    Dim wksSrc As Worksheet
    Dim wksTgt As Worksheet
    Dim vData As Variant
    Dim lngLoop As Long
    Application.ScreenUpdating = False
    Set wksTgt = Worksheets("Sheet1")
    Set rngTgt = wksTgt.Range("B2")
    For Each wksSrc In Worksheets
        If wksSrc.Name Like "*Panel" Then
            vData = wksSrc.Range("AJ5:AJ12").Value
            wksTgt.Range(rngTgt, rngTgt.Offset(0, 7)).Value = WorksheetFunction.Transpose(vData)
            rngTgt.Offset(0, -1).Value = wksSrc.Range("D2").Value
            Set rngTgt = rngTgt.End(xlToRight).Offset(0, 1)
            Set rngSrc = wksSrc.Range("T1").End(xlDown)
            Do Until (rngSrc.Row = wksSrc.Rows.Count) Or (rngSrc.Value = "Space Unusable")
                rngTgt.Value = rngSrc.Value
                rngTgt.Offset(0, 1).Value = rngSrc.Offset(1).Value
                rngTgt.Offset(0, 2).Value = rngSrc.Offset(3).Value
                Set rngTgt = rngTgt.Offset(0, 3)
                Set rngSrc = rngSrc.Offset(3).End(xlDown)
            Set rngSrc = wksSrc.Range("AS1").End(xlDown)
            Do Until (rngSrc.Row = wksSrc.Rows.Count) Or (rngSrc.Value = "Space Unusable")
                rngTgt.Value = rngSrc.Value
                rngTgt.Offset(0, 1).Value = rngSrc.Offset(1).Value
                rngTgt.Offset(0, 2).Value = rngSrc.Offset(3).Value
                Set rngTgt = rngTgt.Offset(0, 3)
                Set rngSrc = rngSrc.Offset(3).End(xlDown)
            Set rngTgt = rngTgt.EntireRow.Cells(1, 2).Offset(1)
        End If
    wksTgt.UsedRange.Cells.SpecialCells(xlCellTypeBlanks).Value = "Space"
    wksTgt.Range("A1:I1").Value = Array("Building", "Name", "Voltage", "Phase/Wire", "Amperage", "AIC", "Fed From", "Through", "Manufacturer")
    Set rngTgt = wksTgt.Range("A1").End(xlToRight).Offset(0, 1)
    wksTgt.Range(rngTgt, rngTgt.Offset(0, 2)).Value = Array("Load", "Trip Setting", "Model")
    Set rngTgt = wksTgt.Range("A1").End(xlToRight).Offset(0, 1)
    For lngLoop = 2 To wksTgt.Columns.Count
        wksTgt.Range(rngTgt, rngTgt.Offset(0, 2)).Value = Split(Replace("Load#^Trip Setting#^Model#", "#", lngLoop), "^")
        Set rngTgt = wksTgt.Range("A1").End(xlToRight).Offset(0, 1)
        If rngTgt.Column >= wksTgt.UsedRange.Columns.Count Then
            Exit For
        End If
    Application.ScreenUpdating = True
End Sub

Open in new window

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.