Personally, I think he should re-design his spreadsheet <bg

Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(

IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of


=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is


=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated
in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.

In this case Roger, I think it needs a BIG re-design. I tried to concoct a
formula where the values were not comma separated in one cell, but many
cells, and it wasn't much better.

This took me a while to figure out, I wouldn't want to come to this
spreadsheet and see that monster unprepared, so I think the maintenance is
probably not worth it.

Start again with the requirement, and come up with a simpler design. Often
works for me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Personally, I think he should re-design his spreadsheet <bg

Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(


IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out



MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of



=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is



=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated
in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.

Again, I feel like thinking aloud even though I don't have a clue of what's going on.

Personally, I think he should re-design his spreadsheet <bg

That was the first thought that came to my mind when I briefly read the post. <g

Unfortunately I've inherited the spreadsheet and attempting to make it work
(without the use of a calculator and pen)....... You guys are way beyond my excel knowledge.

I feel so sorry that Terranoman has to put up with the inheritance.

Bob and Roger, I just want to point out this is exactly why I always focus on and have so many questions on speed etc. I want to learn to do it right (i.e. design the spreadsheet properly) in my early stage of learning so that I can save myself and others from pain and suffering when it comes to composing formulae down the road. I hope you understand my reasons behind my numerous questions. Your support and guidance are always appreciated.

Epinn

"Roger Govier" wrote in message ...
Personally, I think he should re-design his spreadsheet <bg

Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(

IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),

MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of


=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is


=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated
in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.

Epinn,

Nobody minds your questions, you would soon hear if we did <bg.

I can't speak for the OPs though whose threads you hijack <ebg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Again, I feel like thinking aloud even though I don't have a clue of what's
going on.

Personally, I think he should re-design his spreadsheet <bg

That was the first thought that came to my mind when I briefly read the
post. <g

Unfortunately I've inherited the spreadsheet and attempting to make it
work
(without the use of a calculator and pen)....... You guys are way beyond my
excel knowledge.

I feel so sorry that Terranoman has to put up with the inheritance.

Bob and Roger, I just want to point out this is exactly why I always focus
on and have so many questions on speed etc. I want to learn to do it right
(i.e. design the spreadsheet properly) in my early stage of learning so that
I can save myself and others from pain and suffering when it comes to
composing formulae down the road. I hope you understand my reasons behind
my numerous questions. Your support and guidance are always appreciated.

Epinn

"Roger Govier" wrote in message
...
Personally, I think he should re-design his spreadsheet <bg

Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(


IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out



MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of



=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is



=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated
in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.

<< Nobody minds your questions......

Not everyone is as kind and patient as most of you are. I have to be cautious ......

<< ...... hijack ......

I know, Bob. This is a sensitive issue and it has always been a concern for me. I try to apologize before anyone complains. I always seem to have more questions than anyone around. If I think open group discussion, archive, easy reference for future research etc. then I can justify asking my questions or providing my comments under the original poster's thread. Having said this, I do understand that the poster has "ownership" and certain privilege and it is also natural that he/she feels "possessive" and doesn't want others to ask many questions or "take over" ......

I usually hold off my questions until the poster has got the answers and "left" the forum.

What is the right approach? When unsure, shut up?

Epinn

"Bob Phillips" wrote in message ...
Epinn,

Nobody minds your questions, you would soon hear if we did <bg.

I can't speak for the OPs though whose threads you hijack <ebg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Again, I feel like thinking aloud even though I don't have a clue of what's
going on.

Personally, I think he should re-design his spreadsheet <bg

That was the first thought that came to my mind when I briefly read the
post. <g

Unfortunately I've inherited the spreadsheet and attempting to make it
work
(without the use of a calculator and pen)....... You guys are way beyond my
excel knowledge.

I feel so sorry that Terranoman has to put up with the inheritance.

Bob and Roger, I just want to point out this is exactly why I always focus
on and have so many questions on speed etc. I want to learn to do it right
(i.e. design the spreadsheet properly) in my early stage of learning so that
I can save myself and others from pain and suffering when it comes to
composing formulae down the road. I hope you understand my reasons behind
my numerous questions. Your support and guidance are always appreciated.

Epinn

"Roger Govier" wrote in message
...
Personally, I think he should re-design his spreadsheet <bg

Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(


IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out



MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of



=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is



=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated
in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.

Absolutely not. If it were me, I think I would open a new thread as a
discussion thread, referencing back to the thread that made you stop and
think. That way, the OPs thread is left to run its course (we have to be
careful not to intimidate/scare off posters from asking follow-ups), but
your questions get a very clear forum of their own which people can join or
ignore as is there wont.

But never shut up, others will also read the threads and learn from them. I
go the COLUM(A2:F4)^0 trick from this, and I am sure I will use that in
future.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
<< Nobody minds your questions......

Not everyone is as kind and patient as most of you are. I have to be
cautious ......

<< ...... hijack ......

I know, Bob. This is a sensitive issue and it has always been a concern for
me. I try to apologize before anyone complains. I always seem to have more
questions than anyone around. If I think open group discussion, archive,
easy reference for future research etc. then I can justify asking my
questions or providing my comments under the original poster's thread.
Having said this, I do understand that the poster has "ownership" and
certain privilege and it is also natural that he/she feels "possessive" and
doesn't want others to ask many questions or "take over" ......

I usually hold off my questions until the poster has got the answers and
"left" the forum.

What is the right approach? When unsure, shut up?

Epinn

"Bob Phillips" wrote in message
...
Epinn,

Nobody minds your questions, you would soon hear if we did <bg.

I can't speak for the OPs though whose threads you hijack <ebg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Again, I feel like thinking aloud even though I don't have a clue of what's
going on.

Personally, I think he should re-design his spreadsheet <bg

That was the first thought that came to my mind when I briefly read the
post. <g

Unfortunately I've inherited the spreadsheet and attempting to make it
work
(without the use of a calculator and pen)....... You guys are way beyond my
excel knowledge.

I feel so sorry that Terranoman has to put up with the inheritance.

Bob and Roger, I just want to point out this is exactly why I always focus
on and have so many questions on speed etc. I want to learn to do it right
(i.e. design the spreadsheet properly) in my early stage of learning so that
I can save myself and others from pain and suffering when it comes to
composing formulae down the road. I hope you understand my reasons behind
my numerous questions. Your support and guidance are always appreciated.

Epinn

"Roger Govier" wrote in message
...
Personally, I think he should re-design his spreadsheet <bg

Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(


IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out



MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of



=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is



=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated
in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.

Before I forget, I must apologize to the original poster for side tracking. Yes, Bob, opening a new thread and referencing back to the original thread may be the middle ground and I did think about this. I'll start right now. Bob, please move over to "What is the right approach?" thread.

Epinn

"Bob Phillips" wrote in message ...
Absolutely not. If it were me, I think I would open a new thread as a
discussion thread, referencing back to the thread that made you stop and
think. That way, the OPs thread is left to run its course (we have to be
careful not to intimidate/scare off posters from asking follow-ups), but
your questions get a very clear forum of their own which people can join or
ignore as is there wont.

But never shut up, others will also read the threads and learn from them. I
go the COLUM(A2:F4)^0 trick from this, and I am sure I will use that in
future.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
<< Nobody minds your questions......

Not everyone is as kind and patient as most of you are. I have to be
cautious ......

<< ...... hijack ......

I know, Bob. This is a sensitive issue and it has always been a concern for
me. I try to apologize before anyone complains. I always seem to have more
questions than anyone around. If I think open group discussion, archive,
easy reference for future research etc. then I can justify asking my
questions or providing my comments under the original poster's thread.
Having said this, I do understand that the poster has "ownership" and
certain privilege and it is also natural that he/she feels "possessive" and
doesn't want others to ask many questions or "take over" ......

I usually hold off my questions until the poster has got the answers and
"left" the forum.

What is the right approach? When unsure, shut up?

Epinn

"Bob Phillips" wrote in message
...
Epinn,

Nobody minds your questions, you would soon hear if we did <bg.

I can't speak for the OPs though whose threads you hijack <ebg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Epinn" wrote in message
...
Again, I feel like thinking aloud even though I don't have a clue of what's
going on.

Personally, I think he should re-design his spreadsheet <bg

That was the first thought that came to my mind when I briefly read the
post. <g

Unfortunately I've inherited the spreadsheet and attempting to make it
work
(without the use of a calculator and pen)....... You guys are way beyond my
excel knowledge.

I feel so sorry that Terranoman has to put up with the inheritance.

Bob and Roger, I just want to point out this is exactly why I always focus
on and have so many questions on speed etc. I want to learn to do it right
(i.e. design the spreadsheet properly) in my early stage of learning so that
I can save myself and others from pain and suffering when it comes to
composing formulae down the road. I hope you understand my reasons behind
my numerous questions. Your support and guidance are always appreciated.

Epinn

"Roger Govier" wrote in message
...
Personally, I think he should re-design his spreadsheet <bg

Well, funnily enough, that thought also crossed my mind<g
but seeing as he was using the last 2 columns, I wondered whether it was
so horrendous that all columns were being used, and the values couldn't
be split out into other columns. That's why I opted for using 2nd sheet.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
Sorry about the IU IV, I built it using U and V as I could see those
columns.

Personally, I think he should re-design his spreadsheet <bg

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Roger Govier" wrote in message
...
Hi Bob

Phhhewwww.....
That's great.
Just a very small point, the OP was using IU and IV for his columns
for
the lookup table, not U and I and he did not say he was using Sheet2.
I
used a second sheet in my suggestion.
As long as he makes those minor mods, he will have a superb solution.

I had totally missed that there were comma's separating the values in
the source cell in my rather mundane solution which would necessitate
a
change to the formula to
=IF(Sheet1!A1="","",MID(Sheet1!$A1,1+(COLUMN(A1)-1)*4,2))

I think he should go with your solution.

--
Regards

Roger Govier


"Bob Phillips" wrote in message
...
I managed it with a formula that basically looks like this assuming
the
data
is in B2

=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(


IF(ISNUMBER(MATCH(MID(SUBSTITUTE(Sheet2!B2,",","") ,ROW(INDIRECT("1:"&LEN(SUB
STITUTE(Sheet2!B2,",","")))),2),Sheet2!$U$1:$U$50, 0)),


MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0),1))-1,0)))

Unfortunately, that has too many nested functions, so I had to
split
out



MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIREC T("1:"&LEN(SUBSTITUTE(Shee
t2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and define it as a name (Ctrl-F3). I used a name of matches and a
Refersto
value of



=MATCH(MID(SUBSTITUTE(Sheet2!B2,",",""),ROW(INDIRE CT("1:"&LEN(SUBSTITUTE(She
et2!B2,",","")))),2),Sheet2!$U$1:$U$50,0)

and then my formula is



=SUM(TRANSPOSE(OFFSET($V$1,TRANSPOSE(IF(ISNUMBER(m atches),matches,1))-1,0)))

which is an array formula, it should be committed with
Ctrl-Shift-Enter, not
just Enter.

I also had to enter a dummy value in row 1 of your lookup table
with a
weight of 0 to get it to work.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Terranoman" wrote in
message
...
I am attempting to set up a spreadsheet whereby I can summarise
the
weight
load of a vehicle. The cells will contain a variation of
abbreviated
texts
in the same cell e.g.

An example of column B contents is ah, zy, pd, (in a single cell
on
each
row)

Each abbreviation will have it's own unique number (weight)
allocated
in
two
seperate column which the formula will refer to - i.e

Column IU IV
Row 1 ah 2.2
Row 2 zy 3.7
Row 3 pd 1.4

There will be approximately 50 such text references and values.

Currently column b (rows 1 to 50) contain the varied text (ah, zy,
pd
etc
in
a single cell). Is it possible to 'find' the idividual text
abbreviation
(ah, zy or pd) in column B and total the allocated weight using
sumif
or
sumproduct even though they are in the same cell?

Any suggestions greatly appreciated.