Data Pivot Table/Manipulation Question

[Scrubn99]

Hi, I have been working on this problem for a few days now and have tried multiple approaches with no luck. Any help is appreciated.

I am trying to develop a spreadsheet that will reconfigure data from a varying number of rows into one row for each ID. I have been able to use pivot tables and lookups to get one variable to populate in the row column but there are 5 columns that need to be condensed into one. I would like to be able to paste in my data, then run a macro and have an output tab with the correct format...but honestly any help would be appreciated beyond belief...

Please keep in mind this is text, not numbers that I am trying to manipulate. I am also aware that there may be a large number of columns (each ID can have up to 20 rows of five fields which need to be moved to one row each, so we could be looking at 200+ columns) that large number is ok, this is the format that the data needs to be in for our CRM to recognize it.

What they have provided

Identifier

Interpretation Result: Interpretation Result Name

Field Status

Final Result

Reader A Measurement

Reader B Measurement

NEW10-002-001-FUN-FE

PC2fa Fibrovascular PED

Not-Acceptable

No

Can't Grade

NEW10-002-001-FUN-FE

PC2aa GA

Not-Acceptable

No

Can't Grade

NEW10-6789test-001-FUN-SE

PC2fa Fibrovascular PED

Not-Acceptable

No

Yes

NEW10-6789test-001-FUN-SE

PC2aa GA

Acceptable

Yes

Yes

Yes

NEW10-6789test-001-FUN-SE

PC2ba Subretinal Fluid

Acceptable

Yes

Yes

Yes

NEW10-6789test-001-FUN-SE

PC2ea Serous PED

Not-Acceptable

Yes

No

NEW10-6789test-001-FUN-SE

PC2ga Subretinal Fibrosis

Acceptable

Yes

Yes

Yes

What they Need

Identifier

Interpretation Result: Interpretation Result Name

Field Status

Final Result

Reader A Measurement

Reader B Measurement

Interpretation Result: Interpretation Result Name

Field Status

Final Result

Reader A Measurement

Reader B Measurement

Interpretation Result: Interpretation Result Name

Field Status

Final Result

Reader A Measurement

Reader B Measurement

Interpretation Result: Interpretation Result Name

Field Status

Final Result

Reader A Measurement

Reader B Measurement

NEW10-002-001-FUN-FE

PC2fa Fibrovascular PED

Not-Acceptable

No

Can't Grade

PC2aa GA

Not-Acceptable

No

Can't Grade

NEW10-6789test-001-FUN-SE

PC2fa Fibrovascular PED

Not-Acceptable

No

Yes

PC2ba Subretinal Fluid

Acceptable

Yes

Yes

Yes

PC2ea Serous PED

Not-Acceptable

Yes

No

PC2ga Subretinal Fibrosis

Acceptable

Yes

Yes

Yes

Only one id per row…the rest of the data needs to be in order next to it.

Note, the Fields will not change, there will always be an ID + 5 fields

Thanks!

<colgroup><col><col><col span="19"></colgroup><tbody>
</tbody>

 

[Jerry Sullivan]

Hi and Welcome to MrExcel,

You mention PivotTable in your subject line. Do you need this to be in a PivotTable or were you just trying that as one possible solution?

What you describe could be done more easily with VBA and placed in a standard Worksheet Range.

Would an example of a VBA approach be helpful to you?

 

[Scrubn99]

Mr. Sullivan,

Thanks for your reply. It does not need to be a pivot table, that was just the only way I could think to approach it. Any help or examples would be greatly appreciated.

Thanks.

Justin

 

[Jerry Sullivan]

Justin, Thanks for sending me an example file. I understand your requirements changed slightly since your original post- the primary difference being the addition of 5 fields. The data in these 5 fields will be the same for any single Identifier, so like the Identifier itself, those fields do not need to be repeated in the transformation of the data.

Your example file shows these fields in the Output sheet, but they don't appear on the Input sheet. Will the non-repeating fields (Identifier...Eye) appear in Columns A:F of the Input Sheet followed by the repeating fields in Columns G:K?

 

[Scrubn99]

Justin, Thanks for sending me an example file. I understand your requirements changed slightly since your original post- the primary difference being the addition of 5 fields. The data in these 5 fields will be the same for any single Identifier, so like the Identifier itself, those fields do not need to be repeated in the transformation of the data.

Your example file shows these fields in the Output sheet, but they don't appear on the Input sheet. Will the non-repeating fields (Identifier...Eye) appear in Columns A:F of the Input Sheet followed by the repeating fields in Columns G:K?

the non repeating field A will appear on the input sheet, the non repeating fields B:F will not appear on the input sheet. Rather they are derived from the identifier itself. For instance, if the identifier is NEW10-002-001-FUN-FE, then the columns for that identifier on the output would be:

Column A: NEW10-002-001-FUN-FE
Column B: New10
Column C: 002
Column D: 001
Column E: FUN
Column F: FE

From G on the columns need to contain all of the info from each row as per my original specs. I realize this whole set of requirements is ridiculous, but such is life...

Thanks Jerry!

 

[Jerry Sullivan]

Justin, I hadn't even looked to see if there was a relationship between the Identifier and the 5 added columns. If I had, I would hope to have cracked the pattern.

Here's a macro for you to try...

Sub TransformData()
'---This sub takes an Input dataset that can have multiple rows per identifier
'   and transforms it into a Output dataset with one row per identifier.
'   Each row of Output is organized as follows:
'   The first column contains the identifier (a dash "-" delimited code)
'   The next several columns split the identifier into its parts.
'   The remaining columns contain a variable amount of data based the number
'   of rows associated with that identifier in the source dataset

 Dim lLastRow As Long, lReadRow As Long, lReadCol As Long, lNparts As Long
 Dim lWriteRow As Long, lWriteCol As Long, lOuterLoop As Long, lHeaderLoops As Long
 Dim lMaxRowsForIdentifier As Long, lColumnsReqd As Long
 Dim sIdentifier As String
 Dim vInput As Variant, vWriteArray As Variant, vID_Parts As Variant
 Dim wksInput As Worksheet, wksOutput As Worksheet
 
 Const sID_HEADERS = "Study-Subject-Visit-Form"
 '--store number or parts of Id for validation
 lNparts = UBound(Split(sID_HEADERS, "-")) - 1
 
 Set wksInput = Sheets("Input")
 Set wksOutput = Sheets("Output")
 
 '--read dataset including header row into array
 With wksInput
   lLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
   If lLastRow < 2 Then
      MsgBox "No Input found"
      Exit Sub
   End If
   
   vInput = .Range("A1").CurrentRegion.Value
 End With
   
 '--calculate number of columns needed for identifier with most rows
 lMaxRowsForIdentifier = wksInput.Evaluate("MAX(COUNTIF(A2:A" _
   & lLastRow & ",A2:A" & lLastRow & "))")
 
 '--identifer plus non-repeat columns plus max repeat columns
 lColumnsReqd = 1 + lNparts + _
   lMaxRowsForIdentifier * UBound(vInput, 2) - 1

 For lReadRow = 1 To UBound(vInput, 1)
   '--check for new identifier
   If vInput(lReadRow, 1) <> sIdentifier Then
      '--output previous identifier line unless first row
      If lWriteRow > 0 Then
         wksOutput.Cells(lWriteRow, "A").Resize(1, _
            lWriteCol - 1).Value = vWriteArray
      End If
      '--start new row for this identifier
      lWriteRow = lWriteRow + 1
      ReDim vWriteArray(1 To 1, 1 To lColumnsReqd)
      lWriteCol = 1

      '--write values to array for non-repeating columns
      If lReadRow = 1 Then
         sIdentifier = sID_HEADERS
         vWriteArray(1, lWriteCol) = "Identifier"
      Else
         sIdentifier = vInput(lReadRow, 1)
         vWriteArray(1, lWriteCol) = sIdentifier
      End If
      
      vID_Parts = Split(sIdentifier, "-")
      '--check so invalid identifier doesn't cause error
      If UBound(vID_Parts) - 1 <> lNparts Then
         MsgBox sIdentifier & " is an invalid identifier"
         GoTo ExitProc
      End If
      
      For lWriteCol = 2 To UBound(vID_Parts) + 2
         vWriteArray(1, lWriteCol) = vID_Parts(lWriteCol - 2)
      Next lWriteCol
   End If 'new identifier
   
   '--add set of columns of values to array for each row of identifier
   '--for header row, do added loops to fill reqd columns from single input row
   lHeaderLoops = IIf(lReadRow = 1, lMaxRowsForIdentifier, 1)
   For lOuterLoop = 1 To lHeaderLoops
      For lReadCol = 2 To UBound(vInput, 2)
         vWriteArray(1, lWriteCol) = vInput(lReadRow, lReadCol)
         lWriteCol = lWriteCol + 1
      Next lReadCol
   Next lOuterLoop
 Next lReadRow
 
 '--write final row
 wksOutput.Cells(lWriteRow, "A").Resize(1, _
   lWriteCol - 1).Value = vWriteArray

 '--add header formatting then fit columns
 With wksOutput.Cells(1, "A").Resize(1, lColumnsReqd)
   .Interior.Color = 14857357
   .Font.Bold = True
   .EntireColumn.AutoFit
 End With

ExitProc:
 Set wksInput = Nothing
 Set wksOutput = Nothing
End Sub

The code is lengthier than it needs to be for the specific example you provided. Since your project definition seems to be evolving, I've tried use parameters instead of fixed values to allow you to change the numbers of fields with little or no modification of the code.

For example, you could modify the repeated fields (eg add "Reader C Measurement") without changing the code.