how can I fit this in 1 row?

[Rich B]

Hi everyone I hope you can help,

I have one row of information for each person in my excel
sheet. I am going to be pulling some data from a DB and
it is formatted as follows..

One Person can only have one row on the sheet. I need to
find out what percentage someone has got of A & B (They
always together make up to 100% when together) Trouble is
there are different parts making up A and B. I can
uniquely identify them through an identifier created a
sheet called Values laid out in the following format.
I know I can use a VLOOKUP function to look in the Values
sheet.

Identifier Category
591 A
555 A
777 A
888 B
999 B

The information I will be pulling is going to be in the
following format.

Person 1 =
ID - 555 Amount - 50
ID - 777 Amount - 50
ID - 999 Amount - 100

Person 2 =
ID - 591 Amount - 100
ID - 888 Amount - 300

Person 1 has 50% Type A and 50% Type B.
Person 2 has 25% Type A and 75% Type B.

Any Ideas?

Richard

 

[Frank Kabel]

Hi
is your information in one cell or is this in separate columns for one
row?

 

[Rich]

The values coming in are going to be in this format

name-identifier-value

if there was only one identifier and value that would be
easy but with there been many different identifiers which
come into category a or b it is difficult for me to get it
to work.

I havent decided where to put the information yet the only
structures in place are what Ihave posted before

ID

555 - A
666 - A
777 - B
888 - B
999 - B

this is all in a seperate sheet so when the data comes in
as

name-identifier-value
-identifier-value
-identifier-value

if you see what I mean!!!

LOL

 

[rich]

Sorry Frank to answer your question more specifically I
can use a s many rows as I wantthe name will be given to
the sheet once and there will be different numbers of the
sets of ids and values usually no more than 4

 

[Frank Kabel]

Hi Rich
still not colpletly sure but try the following:
1. Some assumptions about your sheet with name, identifiers and values:
- Layout example:

A B C
1 name1 555 20
2 name1 666 30
3 name2 555 70
.....

- I assume that all columns are filled for all rows
- The ID / Type table is on a separate sheets named 'IDs'

2. Use a helper column on your first sheet. e.g. column D. Enter the
following in D1
=IF(B1<>"",VLOOKUP(B1,'IDs'!$A$1:$B$100,2,0),"")
and copy this down for all rows

3. Now to calculate the precentage for one person (e.g. for 'name1')
use the following formulas:
- Type A percentage:
=SUMPRODUCT(--(A1:A100="name1"),--(D1100="A"),C1:C100)/SUMIF(A1:A100,
"name1",C1:C100)

- Type B percentage:
=SUMPRODUCT(--(A1:A100="name1"),--(D1100="B"),C1:C100)/SUMIF(A1:A100,
"name1",C1:C100)
format both resulting cells as percentage

 

[Rich]

Yes that would work frank but sometimes the data is as
follows. as you can see sometimes the person has got
three types of IDs and they are relational to the value
next to them (always the cell to the right) of the ID.
How can I get round this?

A B C D E F G
1 name1 555 25 666 50 777 25
2 name2 666 50 555 50 - -
3 name2 555 70 - - - -
.....

Name 1 has a total of 100 (C+E+G) and 555 and 666 IDs are
type A. 777 is type B then for name a type A % = 75% and
type B % = 25%.

Name 2 has a total of 100 also but as both IDs are type
A ......

I hope that explains it better

Thanks again

 

[David McRitchie]

Hi Rich, and those using the archives,

topic continues at
http://google.com/[email protected]

For the benefit of those who use Google Groups
http://groups.google.com/advanced_group_search?q=group:*Excel*&num=100
to find their answers in previously posted questions and answers, Rich started a new thread
as a continuation of this as "Message for Frank K" posted under a different Alise

I would suggest using your fill name and correct email address
to work best in newsgroups, but at least use your full name so you can
find things you were involved with earlier in time and forgot details.

 

[Frank Kabel]

Hi David
thanks for nthe linkage

--
Regards
Frank Kabel
Frankfurt, Germany

 

[David McRitchie]

Hi Frank,
You're welcome. I neglected to say that it takes 12 hours for postings
to get into the archives so the link to the continuation of the thread
isn't effective just yet.