summarizing data in excell

[apurwanta]

Hi,

I need help please.
I am creating a staff scheduling.

the format is like this
date date date date date date
name client A client A client C client C client D client D
name client B client B client B client E client E client E
name client B client B client C client D client D client D
name client B client B client B client E client E client E
name client A client A client A client E client E client E
name client A client A client C client C
name client D client D
client D

Right now, i am planning to create a summary per client which showing the
staff participated in each client and the date we perform work on the client.

I am thinking to use Vlookup function.
To do so first i make second table which copy the name column between each
column which make the table like this

date name date name date
name client A name client A name client C
name client B name client B name client B
name client B name client B name client C
name client B name client B name client B
name client A name client A name client A
name client A name client A name client C

After i have the table above, then i create next table which will be the
summary per client


date date date date date
client A name name
name name
client B name
name
Client C name name
name name


it should have been easy with vlookup function which looking the client name
in the table 2 and extracting the staff name which is on the column next to
the client name in the table 2.
but the problem is vlookup function will only take the first data it meet.
so if in the same column there are 2 client A, the second client A wont be
taken to table 3.

Could you please help me with the vlookup function?
if it's not possible using vlookup function, what formula should i be using
to summarizing table 1 per client name?

 

[Gordon]

Hi,

I need help please.
I am creating a staff scheduling.

the format is like this
date date date date date
date
name client A client A client C client C client D
client D
name client B client B client B client E client E
client E
name client B client B client C client D client D
client D
name client B client B client B client E client E
client E
name client A client A client A client E client E
client E
name client A client A client C client C
name client D client
D
client D

Right now, i am planning to create a summary per client which showing the
staff participated in each client and the date we perform work on the
client.

I am thinking to use Vlookup function.
To do so first i make second table which copy the name column between each
column which make the table like this

date name date name date
name client A name client A name client C
name client B name client B name client B
name client B name client B name client C
name client B name client B name client B
name client A name client A name client A
name client A name client A name client C

After i have the table above, then i create next table which will be the
summary per client


date date date date date
client A name name
name name
client B name
name
Client C name
name
name
name


it should have been easy with vlookup function which looking the client
name
in the table 2 and extracting the staff name which is on the column next
to
the client name in the table 2.
but the problem is vlookup function will only take the first data it meet.
so if in the same column there are 2 client A, the second client A wont be
taken to table 3.

Could you please help me with the vlookup function?
if it's not possible using vlookup function, what formula should i be
using
to summarizing table 1 per client name?

I think a Pivot Table will be better - and I suggest you post in a dedicated
Excel group rather than a general Office one.

Go here:
news://msnews.microsoft.com/microsoft.public.excel.newusers

PS - It's so long since I used a Pivot Table I wouldn't want to advise on
it!

 

[apurwanta]

Hi Gordon,

thanks for your respond.
As for your suggestion to use pivot table, I don't think it can work since
the data in table is text type (not numerical).
Based on my knowledge, pivot table can only be used for summarizing
numberical data.

Anyway, I'll post the question in excell section.
Again, thank a lot for your respond.

 

[JoAnn Paules]

Pivot tables don't work with test, just numbers.

You could create a quick Access database and run a crosstab query. That's
about the simplest way. (I had to do something very similar - what a PITA to
do it manually. Then I was shown how to do the crosstab query. Would have
been nice to have known how to do it about a week prior.)

 

[apurwanta]

Hi JoAnn,

Thanks for your respond.
Actually i have tried to use access.
I have succeed in importing the table to access.
But the problem is i have no clue in using access.
could you please show me the way.

Pivot tables don't work with test, just numbers.

You could create a quick Access database and run a crosstab query. That's
about the simplest way. (I had to do something very similar - what a PITA to
do it manually. Then I was shown how to do the crosstab query. Would have
been nice to have known how to do it about a week prior.)

--

JoAnn Paules
MVP Microsoft [Publisher]
Tech Editor for "Microsoft Publisher 2007 For Dummies"

I think a Pivot Table will be better - and I suggest you post in a
dedicated Excel group rather than a general Office one.

Go here:
news://msnews.microsoft.com/microsoft.public.excel.newusers

PS - It's so long since I used a Pivot Table I wouldn't want to advise on
it!

 

[db]

what you provided is a little confusing
to me.

I think the mistake is that people try
to make one giant formula and
sometimes try to put all the data
into one spreadsheet.

so my suggestion is not to put all the
formulas into a single spread sheet

instead spread out each sections and place
each one on a separate spread sheet.

----------------

for example begin by placing name 1 on
Sheet 1 A2 and Sheet 2 A2 and Sheet 3 A2
and so on.

name 2 in Sheet 1 A3......

dates in Row 1 in all the sheets

or rearrange / pivot the data on the sheets
above like this:

name 1 at Sheet 1 B1 and Sheet 2 B1...
and Sheet 3 B1....

name 2 at Sheet 1 C1.......

then place the dates in column A in
the sheets.

since the above is simply text you can
use a formula referencing Column A and
Row 1 from Sheet 1 to populate the
identical cells in the other sheets.

what you will accomplish is a cube using
multiple spreadsheets with specific data
and or dedicated formulas.

then use Sheet1 to aggregate you data
from the cube which comprises Sheet 2
- Sheet (whatever) - nice and neat.

--
db·´¯`·...¸><)))º>
DatabaseBen, Retired Professional
- Systems Analyst
- Database Developer
- Accountancy
- Veteran of the Armed Forces
- @Hotmail.com
- nntp Postologist
~ "share the nirvana" - dbZen

~~~~~~~~~~~~~~~

 

[JoAnn Paules]

I did it once. Access is my weakness. Post your question over in the Access
newsgroup. Those Access gurus are very helpful folks. They tolerate my
stupid questions and are quite friendly!

--

JoAnn Paules
MVP Microsoft [Publisher]
Tech Editor for "Microsoft Publisher 2007 For Dummies"

Hi JoAnn,

Thanks for your respond.
Actually i have tried to use access.
I have succeed in importing the table to access.
But the problem is i have no clue in using access.
could you please show me the way.

Pivot tables don't work with test, just numbers.

You could create a quick Access database and run a crosstab query. That's
about the simplest way. (I had to do something very similar - what a PITA
to
do it manually. Then I was shown how to do the crosstab query. Would have
been nice to have known how to do it about a week prior.)

--

JoAnn Paules
MVP Microsoft [Publisher]
Tech Editor for "Microsoft Publisher 2007 For Dummies"

Hi,

I need help please.
I am creating a staff scheduling.

the format is like this
date date date date date
date
name client A client A client C client C client D
client D
name client B client B client B client E client E
client E
name client B client B client C client D client D
client D
name client B client B client B client E client E
client E
name client A client A client A client E client E
client E
name client A client A client C client C
name client D
client D
client D

Right now, i am planning to create a summary per client which showing
the
staff participated in each client and the date we perform work on the
client.

I am thinking to use Vlookup function.
To do so first i make second table which copy the name column between
each
column which make the table like this

date name date name date
name client A name client A name client C
name client B name client B name client B
name client B name client B name client C
name client B name client B name client B
name client A name client A name client A
name client A name client A name client C

After i have the table above, then i create next table which will be
the
summary per client


date date date date date
client A name name
name name
client B name
name
Client C name name
name
name


it should have been easy with vlookup function which looking the
client
name
in the table 2 and extracting the staff name which is on the column
next
to
the client name in the table 2.
but the problem is vlookup function will only take the first data it
meet.
so if in the same column there are 2 client A, the second client A
wont
be
taken to table 3.

Could you please help me with the vlookup function?
if it's not possible using vlookup function, what formula should i be
using
to summarizing table 1 per client name?

I think a Pivot Table will be better - and I suggest you post in a
dedicated Excel group rather than a general Office one.

Go here:
news://msnews.microsoft.com/microsoft.public.excel.newusers

PS - It's so long since I used a Pivot Table I wouldn't want to advise
on
it!