This course will introduce you to C++ 11 and teach you about syntax fundamentals.
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,
Yan
Tracking_.xlsm
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
Who is Participating?
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
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.
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
'~~~~~~~~~~~~~~~~~~~~~~~~~
C--Users-cdy-Desktop-Tracking_---no.xlsx
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
'~~~~~~~~~~~~~~~~~~~~~~~~~
C--Users-cdy-Desktop-Tracking_---no.xlsx
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
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
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
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
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
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))),"")
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
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
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
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.
Experts Exchange Solution brought to you by
Enjoy your complimentary solution view.
Get every solution instantly with Premium.
Start your 7-day free trial.
Thanks
Rob H