Ayansane

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,

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

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

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

@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

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

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

ASKER

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

You're welcome Yan! Glad to help.

Thanks for the feedback.

Thanks for the feedback.

you are welcome

Thanks

Rob H