Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I fit this in 1 row?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I fit this in 1 row?
Hi
is your information in one cell or is this in separate columns for one row? -- Regards Frank Kabel Frankfurt, Germany Rich B wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I fit this in 1 row?
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 -----Original Message----- 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 . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I fit this in 1 row?
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 -----Original Message----- Hi is your information in one cell or is this in separate columns for one row? -- Regards Frank Kabel Frankfurt, Germany Rich B wrote: 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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I fit this in 1 row?
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"),--(D1:D100="A"),C1:C100)/SUMIF(A1:A100, "name1",C1:C100) - Type B percentage: =SUMPRODUCT(--(A1:A100="name1"),--(D1:D100="B"),C1:C100)/SUMIF(A1:A100, "name1",C1:C100) format both resulting cells as percentage -- Regards Frank Kabel Frankfurt, Germany Rich wrote: 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 -----Original Message----- 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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I fit this in 1 row?
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 -----Original Message----- 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"),-- (D1:D100="A"),C1:C100)/SUMIF(A1:A100, "name1",C1:C100) - Type B percentage: =SUMPRODUCT(--(A1:A100="name1"),-- (D1:D100="B"),C1:C100)/SUMIF(A1:A100, "name1",C1:C100) format both resulting cells as percentage -- Regards Frank Kabel Frankfurt, Germany Rich wrote: 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 -----Original Message----- 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 . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I fit this in 1 row?
Hi Rich, and those using the archives,
topic continues at http://google.com/groups?threadm=db5...80a@ phx..gbl For the benefit of those who use Google Groups http://groups.google.com/advanced_gr...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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Rich" wrote in message ... Yes that would work frank but sometimes the data is as follows .... |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I fit this in 1 row?
Hi David
thanks for nthe linkage :-) -- Regards Frank Kabel Frankfurt, Germany David McRitchie wrote: Hi Rich, and those using the archives, topic continues at http://google.com/groups?threadm=db5...a101280a@ phx ..gbl For the benefit of those who use Google Groups http://groups.google.com/advanced_gr...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. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Rich" wrote in message ... Yes that would work frank but sometimes the data is as follows .... |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
how can I fit this in 1 row?
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. "Frank Kabel" wrote in message ... Hi David, thanks for the linkage :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|