Solved

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

Posted on 2016-09-14
9
70 Views
Last Modified: 2016-09-16
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,
Yan
Tracking_.xlsm
0
Comment
Question by:Ayansane
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 32

Expert Comment

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

Thanks
Rob H
1
 
LVL 23

Expert Comment

by:Danny Child
ID: 41799502
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
C--Users-cdy-Desktop-Tracking_---no.xlsx
1
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41799524
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.

Thanks
Rob
1
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 41799566
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:

=A4&"-"&COUNTIF(A$3:A4,A4)

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

Thanks
Rob H
1
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41799603
@Ayansane

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
1
 
LVL 29

Accepted Solution

by:
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41801043
Hi Ayansane,

You may try the following Array Formula in E4 which requires confirmation with Ctrl+Shift+Enter instead of Enter alone and copy it down to see if this is what you are trying to achieve...

In E4
=IFERROR(INDEX(Details!$E$3:$E$42,SMALL(IF(Details!$A$3:$A$42=A4,ROW(Details!$A$3:$A$42)-ROW(Details!$A$3)+1),COUNTIF(A$4:A4,A4))),"")

Open in new window

Just have the similar formula for other columns as well to see if that resolves your issue.
1
 

Author Comment

by:Ayansane
ID: 41801086
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.

Best,
Yan
0
 
LVL 29

Expert Comment

by:Subodh Tiwari (Neeraj)
ID: 41801105
You're welcome Yan! Glad to help.
Thanks for the feedback.
1
 
LVL 25

Expert Comment

by:ProfessorJimJam
ID: 41801263
you are welcome
1

Featured Post

Are your AD admin tools letting you down?

Managing Active Directory can get complicated.  Often, the native tools for managing AD are just not up to the task.  The largest Active Directory installations in the world have relied on one tool to manage their day-to-day administration tasks: Hyena. Start your trial today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question