Searching within a single cell and summing up relevant values

[iambong]

Hi,


i have a single cell that has data in the following format


"name age sex height weight date name(1) age(1) sex(1) height(1) weight(1) date(1) name(2) age(2) sex(2) height(2) weight(2) date(2) name(3) age(3) sex(3) height(3) weight(3) date(3)"


example: "tom 41 male 190 80 5/12/2015 bill 29 male 174 70 5/14/2015 sarah 19 female 140 45 5/14/2015 cheryl 29 female 170 80 5/14/2015"


1. the terms are all separated by a single space
2. i cant use text to column


How may i use excel formula or vba to sum the weight of all the applicants with a particular date?


i.e. i want to sum the weight of bill, sarah and cheryl to obtain 195 as their 3 data series are dated 5/14/2015




Thank you for the help in advance!

 

[Rijnsent]

Hi iambong,

you're making it really hard on yourself by stating point 2: no text to columns... That would by far be the easiest approach... So you asked for a complicated answer, this could help you:

From this answer I could find the formula to find the nth space in a text:
http://www.mrexcel.com/forum/excel-questions/72148-formula-find-nth-space-text-string.html
If you put your example in cell A2, the formula would be =SEARCH(CHAR(127);SUBSTITUTE($A2;" ";CHAR(127);5))
That 5th space is the start of your first date (criteria). The answer should be 19, if you then use =TRIM(MID($A2;19;11)), you get the text of the date "5/12/2015". I'm using 11 characters and a TRIM because dates can be 10 characters long (in november & december).
The weight of that first person is between the 4th and 5th space, so =SEARCH(CHAR(127);SUBSTITUTE($A2;" ";CHAR(127);4)) and =SEARCH(CHAR(127);SUBSTITUTE($A2;" ";CHAR(127);5)). That would give 16 and 19. Those two numbers you could use to grab the weight of that person: =MID($A2;16;19-16)*1. Then all there remains is an IF function and a cell with your criterium-date.

Hope these pointers get you started. But as said: it's way easier if you simply do text to columns.

Cheers,

Koen

 

[iambong]

Hi Rijnsent,


thank you for the help. I am still exploring your solution. But unfortunately i have to state my problem more precisely.


I have to change the way i framed the problem because i realised the data has other problems (sorry, have to desensitise data)



I am looking to




=> sum all the "variable length" values which comes before a "desired" date. and unfortunately the data is all within a single cell (can't delimit it due to other reasons).
=> in this case (in the logic of data), i am referring to the change in wealth

===============

Data and desired output
===============

Data	Input - Desired Date	Output
Tristan Watson Canada 60000 0 -20 5/03/2015 Geraldine Italy 4500 0.5 1500 5/03/2015 Melissa Italy 55000 10 50000 5/03/2015 Tristan Watson Canada 60000 0 -20 5/03/2015 Desmond Hutu Italy 30 0.5 10 5/01/2015 Vincent Dexter Alex Chris Italy 7200 0.013 90 5/03/2015 Geraldine Italy 4500 0.5 1500 5/03/2015 Bill Christian Canada 12830 -0.004 -56 5/02/2015 Jonathan Loda Lucas Canada 75 175 65 5/02/2015 Tristan Watson Canada 34 177 70 5/03/2015	5/3/2015	-20
Tristan Watson Canada 60000 0 -20 5/03/2015 Geraldine Italy 4500 0.5 1500 5/03/2015 Melissa Italy 55000 10 50000 5/03/2015	5/3/2015	51480
Tristan Watson Canada 60000 0 -20 5/03/2015 Desmond Hutu Italy 30 0.5 10 5/01/2015 Vincent Dexter Alex Chris Italy 7200 0.013 90 5/03/2015 Geraldine Italy 4500 0.5 1500 5/03/2015 Bill Christian Canada 12830 -0.004 -56 5/02/2015	5/3/2015	1570
Sheralyn Lim Canada 3450 0.012 40 5/02/2015 Justin Italy 30 0.5 10 5/01/2015	5/3/2015	0
Tristan Watson Canada 60000 0 -20 5/03/2015	5/3/2015	-20
Bill Christian Canada 12830 -0.004 -56 5/02/2015 Geraldine Italy 4500 0.5 1500 5/03/2015	5/3/2015	1500
Justin Italy 30 0.5 10 5/01/2015 Melissa Italy 55000 10 50000 5/03/2015	5/3/2015	50000

<tbody>
</tbody>

===============
Logic of the data
===============

Name	Place	Existing wealth	% of wealth	Change in wealth	Date
Tom	USA	100	0.111	10	2-May
Bill Christian	Canada	12830	-0.004	-56	2-May
Sheralyn Lim	Canada	3450	0.012	40	2-May
Cheryl Weatherman	Canada	34	-0.726	-90	2-May
Sylvia bin mohammad ashish	Canada	3	-1.064	50	2-May
Nikki Flower	Canada	4000	0.018	70	2-May
Jonathan Loda Lucas	Canada	12000	0.2	2000	2-May
Michael Tan	Canada	333	0.5	111	3-May
Tristan Watson	Canada	60000	0.000	-20	3-May
Geraldine	Italy	4500	0.5	1500	3-May
Melissa	Italy	55000	10.000	50000	3-May
Vincent Dexter Alex Chris	Italy	7200	0.013	90	3-May
Desmond Hutu	Italy	30	0.5	10	1-May
Justin	Italy	30	0.5	10	1-May

<tbody>
</tbody>

Note:

Note that the Length of the name is variable
Note that the existing wealth and change in wealth is of variable length
Note that the change in wealth could be positive or negative sign
Note that the % of wealth could be up to 3 decimal places (it will try to have as few as possible)
The data field comprises of different concatenated combinations of concatenations of the rows of data




Thank you in advance

 

[Rijnsent]

Hi iambong,

that is indeed a nasty problem. I assume, having seen this bit of data, that the values you want to add are located just before the date. So the logic is: if the date can be found, take the value before it. That value has a space before it and a space after it, the space after is being the separator between it and the date. Maybe I'm describing it in a complicated way, but for programming one has to be exact .
Given those parameters, I created the follwing function:

Function GetTotalsForDate(InputString As String, DateString As String) As Double

GetTotalsForDate = 0

If Len(InputString) > 0 And Len(DateString) > 0 Then
    DtFound = InStr(InputString, DateString)
    If DtFound > 0 Then
        GetTotalsForDate = 0
        Do While DtFound > 0
            EndPoint = DtFound - 1
            StartPoint = InStrRev(InputString, " ", EndPoint - 1)
            ValueFound = Mid(InputString, StartPoint, EndPoint - StartPoint) * 1
            GetTotalsForDate = GetTotalsForDate + ValueFound
            DtFound = InStr(EndPoint + 2, InputString, DateString)
        Loop
    End If
End If

End Function

This should extract the right values based on your data. Note that the date (5/03/2015) should be entered as text (a string). Add this function to a module in your workbook and then use the formula =GetTotalsForDate(A2;B2).

I hope that solves your issue!

Cheers,

Koen

 

[István Hirsch]

This formula works if the date to process is entered into col B:

Enter into C1 with Ctrl-Shift-Enter and copy down:

=SUM(IFERROR(1*TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",99)),FIND("#",SUBSTITUTE(SUBSTITUTE(A1," ",REPT(" ",99)),B1,"#",ROW($1:$30)))-199,198)),0))

Excel Workbook
	A	B	C
1	Tristan Watson Canada 60000 0 -20 5/03/2015 Geraldine Italy 4500 0.5 1500 5/03/2015 Melissa Italy 55000 10 50000 5/03/2015	5/03/2015	51480
2	Sheralyn Lim Canada 3450 0.012 40 5/02/2015 Justin Italy 30 0.5 10 5/01/2015	5/03/2015	0
3	Tristan Watson Canada 60000 0 -20 5/03/2015	5/03/2015	-20
4	Justin Italy 30 0.5 10 5/01/2015 Melissa Italy 55000 10 50000 5/03/2015	5/03/2015	50000
5	Tristan Watson Canada 60000 0 -20 5/03/2015 Geraldine Italy 4500 0.5 1500 5/03/2015 Melissa Italy 55000 10 50000 5/03/2015 Tristan Watson Canada 60000 0 -20 5/03/2015 Desmond Hutu Italy 30 0.5 10 5/01/2015 Vincent Dexter Alex Chris Italy 7200 0.013 90 5/0	5/03/2015	53120
6	Bill Christian Canada 12830 -0.004 -56 5/02/2015 Geraldine Italy 4500 0.5 1500 5/03/2015	5/03/2015	1500
7	Justin Italy 30 0.5 10 5/01/2015 Melissa Italy 55000 10 50000 5/03/2015	5/03/2015	50000
Sheet

 

[apo]

IamBong...

Please don't cross post without adding links to all associated cross posts..

Searching within a single cell and summing up relevant values

 

[iambong]

my apologies for the cross-posting, am very new to the forums. thank you rijnsent and Istvan hirsch! both of your solutions work. thank you so much