Avatar of Ayansane
Flag for Guinea asked on

Need help getting records "LINED-UP" the proper way (Real Challenge)

Hello Experts,

So I have this Excel spreadsheet with formulas pulling values from a couple sheets.

The ISSUE :(  is that the table is not "dynamically" populating adjacent rows with related data to {Column A}, {Column F}, {Column G} as it should.  If data dynamically updates, this will ensure validity inherent data values to make sense.

In Tracking Sheet:
Columns which require a "SUMIF-like" formula: E, F, G, H, N, O, P, Q (in yellow)

These columns are ok: A, B, C, D, J, K, L, M

Thank you, Thank you, Thank you,
Microsoft ExcelSpreadsheets

Avatar of undefined
Last Comment
Professor J

8/22/2022 - Mon
Rob Henson

Can you upload a non macro version of the file? I can't download macro enabled files but will happily take a look.

Rob H
Danny Child

It looks like the macros are essential, as it's got User Defined Functions on it.

I've saved a copy as XLSX to help other experts, but that's as far as I can go!

Here's the code that I could see
'Yansane Sep 14 2016 ~~~~~~~~ CODE START
Function GetDetails( _
   psInitiativeID As String _
   , psOrderType As String _
   , psOrderSubType As String _
   ) As String
'160913, 14 crystal (strive4peace)
   'to call, use this formula in a cell:
   '  =GetDetails(A4, F4)
   ' where:
   '    4 is whatever row number you are on
   '    column A is the Initiative ID
   '    column F is the Order Type
   'initialize return value
   GetDetails = ""
   'dimension variables
   Dim nRow1 As Long _
      , nRow2 As Long _
      , vReturn As Variant _
      , nRow As Long
   'initialize values
   vReturn = Null
   nRow1 = 2
   With Sheets("Details")
      'get last row of data on the Details sheet
      nRow2 = .Cells(.Rows.Count, 1).End(xlUp).Row  'xlUp=-4162
      'loop through rows and look for the Initiative ID
      For nRow = nRow1 To nRow2
         If .Cells(nRow, 1) = psInitiativeID Then
            'see if Order Type is also the same
            'F is column 6
            If .Cells(nRow, 6) = psOrderType Then
               If .Cells(nRow, 7) = psOrderSubType Then
                  'add to what is already there
                  'seperate values with comma
                  'H is column 8
                  'I is column 9
                  vReturn = (vReturn + ", ") & .Cells(nRow, 9) & " " & .Cells(nRow, 8)
               End If
            End If
         End If
      Next nRow
         Debug.Print psInitiativeID, vReturn
   End With

   GetDetails = vReturn
End Function
'~~~~~~~~~~~~~~~~~~~~~~~~~~~ CODE END
Rob Henson

Your formulas in columns N to Q appear to be working, what is the issue with those?

It seems there is an issue when you have multiple lines relating to one Initiative ID, eg CA025-1-LAN-LC2 has 3 lines in both Tracking and Detail. What are you expecting against each of these?

Maybe you need to add a line identifier where there are multiple lines and then use a INDEX/MATCH combination against the Line ID.

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Rob Henson

Looks like the GetDetail Function in the code pulls the detail of an initiative into column J of Tracking based on entries in columns A, F & G.

Adding a column to the left of Initiative to add a line ID seems to mess this up so try the following in column R:


You can then use this as a unique identifier for each line for the INDEX / MATCH formulas.

Rob H
Professor J


i remember i have helped you before in two other questions.

so, here you go.  there is a free utility in excel that aligns your date. you can download a copy from here
Subodh Tiwari (Neeraj)

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.

Hey fellas,

Thanks a million.  I used Ctrl+Shoft+Enter to capture the array into relevant columns and it worked fine.
I knew I could count on you guys.

Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Subodh Tiwari (Neeraj)

You're welcome Yan! Glad to help.
Thanks for the feedback.
Professor J

you are welcome