Searching using mulitple comma delimited values

[Larry A123]

One tab has two columns. A lists of tasks to be performed based on unique Activity IDs and a column for Charge Numbers.

Each Activity ID can have multiple Charge Numbers in an adjacent cell on its row. The charge numbers are separated by columns in the Charge Number cell. There can be from 1 to 5 charge numbers for each Activity ID.

A second tab has a list of charging, for each month year to date, for each charge number. There is only 1 charging record for each Charge Number.

The charge number in this file is a combination of a department code (9 characters) and the Activity ID Charge Number. The department code is always the same for all records in the Charging tab (file)

Need to search the charging tab and sub total, YTD, all charges for each Activity ID

Column A Column B
Charge Number Activity ID (tasks)
AAMK, ACMK, AEMK, APMK A02C ;note - all charge numbers for a specific Activity ID are contained in 1 cell
AEDA A028
ARHU D02A
TAMK, TCMK, TFMK A029


Column A B C D E F G ... ... M N
Charging File
Full Charge Number Jan Feb Mar April May June July ... ... Dec Total
123456789 AEDA 2,000 500.00 500.00 1,000 4,000 2,000 10,000
123456789 TAMK 1,000 5,500 2,000 6,000 1,500 3,000 19,000
123456789 ....
...
...

Results:

Column A B
Activity ID YTD Total $$
A02C $***X
A028 $***X
D02A $***X

[SamT]

I don't see the problem.

First, I assume that by"Charging Tab" and "Charging File," you mean the same Worksheet, one with a Tab Name of "Charging"
Further,I assume that you want the YTD subtotals on yet a third Worksheet.

Of what significance is the first Worksheet ("Tab") you mentioned, ("One tab has two columns. A lists of tasks to be performed based on unique Activity IDs and a column for Charge Numbers.") to this issue?

All I see is the need for is a very simple formula. Assuming (Yet Again) that the "Activity IDs" on the Results Worksheet are in the same order as the "Full Charge Number" on the "Charging" Worksheet, the Formula in Column Cell next to the top "Activity ID" is simple.
=SUM(Select the appropriate 12 monthly charges cells on the "Charging" Worksheet here)
Then copy the formula down the worksheet to match the bottom "Activity ID."

[Larry A123]

I don't see the problem.

First, I assume that by"Charging Tab" and "Charging File," you mean the same Worksheet, one with a Tab Name of "Charging"
Further,I assume that you want the YTD subtotals on yet a third Worksheet.

Of what significance is the first Worksheet ("Tab") you mentioned, ("One tab has two columns. A lists of tasks to be performed based on unique Activity IDs and a column for Charge Numbers.") to this issue?

All I see is the need for is a very simple formula. Assuming (Yet Again) that the "Activity IDs" on the Results Worksheet are in the same order as the "Full Charge Number" on the "Charging" Worksheet, the Formula in Column Cell next to the top "Activity ID" is simple.
=SUM(Select the appropriate 12 monthly charges cells on the "Charging" Worksheet here)
Then copy the formula down the worksheet to match the bottom "Activity ID."

[SamT]

One tab has two columns. A lists of tasks to be performed based on unique Activity IDs and a column for Charge Numbers.

Each Activity ID can have multiple Charge Numbers in an adjacent cell on its row. The charge numbers are separated by columns in the Charge Number cell. There can be from 1 to 5 charge numbers for each Activity ID.

A second tab has a list of charging, for each month year to date, for each charge number. There is only 1 charging record for each Charge Number.

The charge number in this file is a combination of a department code (9 characters) and the Activity ID Charge Number. The department code is always the same for all records in the Charging tab (file)

Need to search the charging tab and sub total, YTD, all charges for each Activity ID

Column A Column B
Charge Number Activity ID (tasks)
AAMK, ACMK, AEMK, APMK A02C ;note - all charge numbers for a specific Activity ID are contained in 1 cell
AEDA A028
ARHU D02A
TAMK, TCMK, TFMK A029


Column A B C D E F G ... ... M N
Charging File
Full Charge Number Jan Feb Mar April May June July ... ... Dec Total
123456789 AEDA 2,000 500.00 500.00 1,000 4,000 2,000 10,000
123456789 TAMK 1,000 5,500 2,000 6,000 1,500 3,000 19,000
123456789 ....
...
...

Results:

Column A B
Activity ID YTD Total $$
A02C $***X
A028 $***X
D02A $***X

[Larry A123]

Hi Sam, thank you for the quick reply and glad to hear that this is not difficult.
All data are in one workbook. Two tabs, one with the charging history and one that associates multiple charge numbers with an Activity ID (which is a task to be worked).

The charges by charge number are in random order.

There may be multiple charge numbers for each Activity ID.

A "results" tab would show the total charging for each Activity ID. Or the cost could just be on the same line as the "charge numbers" and "activity ID" (see below)

The challenge I find is how to do multiple searches when the search is based on charge numbers in one cell separated by commas. Kind of like selecting charging data and then doing a "Sum | Find(A02B, D2A3, TXMK, T0TW)" (this is just a pretend formula) where the result would be the total $$ for the "find" of those 4 charge
numbers.

A B C
1 Charge Number Activity ID YTD Cost
2 A02B, D2A3, TXMK, T0TW Task n sub total here ;cell "A2" contains "A02B, D2A3, TXMK, T0TW"

Thank you again for jumping in to assist. So far none of our excel experts here can figure this out some program to parse the "Charge Number" cell to separate out all the numbers to do a search then sub total.

Larry

[SamT]

You need two more posts before you can use "Go Advanced" to upload a workbook. So...

Go to our Introductions Forum and Introduce yourself
Reply to this post

Then... Go Advanced and upload a Redacted copy of the Workbook. The Upload must be entirely self referential. ie, all "Charge Numbers," "Activity IDs," Prices, etc on all sheets, including the required manually computed "Results" Sheet" must be the same.

It only needs to have a couple of rows on each sheet and only a couple of "whatevers" in each cell. Just enough for us to get an idea of what's what.

[Larry A123]

Hope this makes the necessary posts so I can attach my file -
thx!

[Larry A123]

the file is attached.

Need to fill in Column "C" in Activity ID tab. Some Activity IDs have as many as 5 charge numbers. These charge numbers are then appended, with a space between, to a 9 character organizational code number to form the complex charge number on the charging tab. The charging tab shows the charging over time for each charge number.

Need to find and total all charges for each Activity ID.

[SamT]

The values in Columns Jan, Feb, and Mar on the Charging sheet are not numbers!
Did you cut and paste them?

They must be converted some time before or during the calculations. It would be best if they were all converted on the original sheet.

To do that place this sub in the Charging Sheet Code Module, then select all the charges, then run the sub
Option Explicit

Sub ConvertNumericalText2ToNumbers()
Dim Cel As Range

For Each Cel In Selection
With Cel
.Value = Trim(.Value)
If Len(.Value) = 1 Then .Value = ""
If .Value <> "" Then .Value = CDbl(.Value)
End With
Next
End Sub


It in your case, the conversion must be done during calculation, I hate you. but let us know so we can code for the conversion

[Larry A123]

the attached file has currency for the charging info.

thx

[SamT]

Working...........

THis is the preface to a UDF that you will be able to use in a cell Formula anywhere on any sheet in the workbook.


Option Explicit
Option Base 1 'Sets the Lower Boundary of Arrays to 1 for standardized
' cross reference to the ChargesTable Rows

Dim ChargesTable As Range
Dim ChargeNumbersList As Variant


Private Sub InitChargeTable()
'If it already exists, get outa here
If Not ChargeTable Is Nothing Then Exit Sub

'Address of top January charges cell
Const FirstCelAddress As String = "$B$3" 'Adjust as need for production
Const FirstChargeNum As String = "$A$3" 'Adjust as need for production
Dim LastCel As Range

With Sheets("Charging") 'Adjust as need for production

'the last cell is always 12 (months) Cells to the right of the last Charge number
Set LastCel = .Range(FirstChargeNum).End(xlDown).Offset(, 12)
Set ChargesTable = Range(.Range(FirstCelAddress), LastCel)
End With
End Sub


Private Sub InitChargeNumbersList()
'If it already exists, get outa here
If Not ChargeTable Is Nothing Then Exit Sub

Const FirstChargeNum As String = "$A$3" 'Adjust as need for production
Dim LastCel As Range

With Sheets("Charging") 'Adjust as need for production
'the last cell is always the bottom of the list
Set LastCel = .Range(FirstChargeNum).End(xlDown)
'Load the ChargeNumbersList array with Chargenumber Values
ChargeNumbersList = Range(.Range(FirstChargeNum), LastCel).Value
End With
End Sub



Public Function YTDCharges(ChargeNums As Range) As Double
'Working on ATT

You will use it like =YTDCharges('Activity ID'!A3) in any cell
-

[SamT]

Having issues. Must reinstall Excel. Back later.

@ AnyBody... Try running this workbook by making the Activity ID sheet calculate, and by Running "Sub t" in Module 1.

Maybe it's not my computer.

[Larry A123]

Thanks all! I have been watching Youtube videos on how to do UDFs. So far I figured out how to display the VBA ribbon and I did a UDF that says "Hello". That is where I'm at, but I'll continue to try more advanced beginner UDFs. I won't complain if someone does this for the example workbook and shares it out as an example...
Larry

[SamT]

Larry, My Excel Install failed. Don't know when I will get it done. I am asking someone else to take over.

Sorry,
SamT

[Larry A123]

NP. I'm spending my time going through VBA videos. I tried the script below and got it to execute. For some reason it only clears out the contents of the selected cells.

Thx again,
Larry

Option Explicit

Sub ConvertNumericalText2ToNumbers()
Dim Cel As Range

For Each Cel In Selection
With Cel
.Value = Trim(.Value)
If Len(.Value) = 1 Then .Value = ""
If .Value <> "" Then .Value = CDbl(.Value)
End With
Next
End Sub

[SamT]

clears out the contents of the selected cells.

Hunh? You mean that it emptys the cells?

[Larry A123]

Yes. The line below looks like it does a test and if positive it blanks out the cell? for testing I inserted the work "test" and "test" is displayed in the first cell of the range. The values in the remaining cells are deleted.

If Len(.Value) = 1 Then .Value = ""
If Len(.Value) = 1 Then .Value = "test"

[SamT]

The first sample file you uploaded, all the "numbers"were Strings, preceded by spaces for alignment purposes.
Value = Trim(Value) gets rid of leading and trailing spaces.

At least one Cell had a dash, AKA Minus sign, also with leading and trailing spaces. That single dash is the reason for
If Len(.Value) = 1 Then .Value = ""

Try it this way

Sub ConvertNumericalText2ToNumbers()
Dim Cel As Range

For Each Cel In Selection
With Cel
.Value = Trim(.Value)
If .Value <> "" Then .Value = CDbl(.Value)
End With
Next
End Sub