Solved

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

Posted on 2016-09-14
9
72 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 33

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 33

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
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 33

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 26

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 30

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 30

Expert Comment

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

Expert Comment

by:ProfessorJimJam
ID: 41801263
you are welcome
1

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
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!
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

830 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