aligning data

[carl669]

never really needed to use excel in the past, so apologies if this is
dumb question.

let's say i have 4 sets of data. each set is 2 columns. 1st column i
a code, second is an occurrence count.

between the 4 sets, any particular code may or may not be in the dat
set.

what i'd like to know is, is there a built in function that will alig
all the codes and their counts for me?

i need to keep the individual data sets, so i can't just count up al
the individual codes and give the total. i've attached a couple sample
since this is kind of hard to explain. the first is the unsorted data
the second is the desired output.

is there a built in function that will do this? if not, can anyon
point me in the right direction? i don't mind if this will take a bi
of work because it will save my group a LOT of time down the road

+-------------------------------------------------------------------
|Filename: test desired output.xls
|Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=86
+-------------------------------------------------------------------

 

[Simon Lloyd]

Wouldn't you be better off using a pivot table?, the format you ar
aiming for seems quite messy. How is the data collected?, what does i
denote - why the separate blocks of columns counting or recording th
same thing

never really needed to use excel in the past, so apologies if this is
dumb question

let's say i have 4 sets of data. each set is 2 columns. 1st column is
code, second is an occurrence count

between the 4 sets, any particular code may or may not be in the dat
set.

what i'd like to know is, is there a built in function that will alig
all the codes and their counts for me

i need to keep the individual data sets, so i can't just count up al
the individual codes and give the total. i've attached a couple sample
since this is kind of hard to explain. the first is the unsorted data
the second is the desired output

is there a built in function that will do this? if not, can anyon
point me in the right direction? i don't mind if this will take a bit o
work because it will save my group a LOT of time down the road

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[carl669]

i don't really like the end format either, but there is a reason to th
madness. basically, we need to keep the data separate to help trac
down errors. the data comes from multiple queries of a client'
database. so, if we keep everything separate, it's easier for us t
eyeball if a particular code looks high or low, and then have the clien
go back fix any data errors we find.

 

[Simon Lloyd]

i don't really like the end format either, but there is a reason to th
madness. basically, we need to keep the data separate to help track dow
errors. the data comes from multiple queries of a client's database. so
if we keep everything separate, it's easier for us to eyeball if
particular code looks high or low, and then have the client go back fi
any data errors we find



--- Automerged consecutive post before response --
well...it turns out that we do need the overall total for each code a
well. so maybe that's a better starting point

i played with the pivot table but can't seem to get it to work over al
4 sets of data

i end up with something like this
Code/RecCnt/Grandtota
HMCM 21 2
HMCM 7
(in the individual data, HMCM is in all 4 columns with counts o
18,7,3,51 so the grand total should be 79

so, i'm obviously doing something wrong. this is a good starting point
although i'd still be interested in the original thing i posted about.I'll take a look at your data again and give you some suggestions an

further help tomorrow

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com

 

[carl669]

thanks for the help.

i've figured out the pivot table to get the overall total for eac
code. so, any help on the original issue would be great. thanks again

 

[Shane Devenshire]

Hi,

I could not see your sample data. (The registration process su.., went
through the 20 steps and then is rejected the input and cleared most of the
fields. Extremely bad design, besides why do they need to know your age,
weight, social security number and visa?) A little exageration here.

My suggestion is that you look at the command Data, Consolidate. Multiple
consolidation ranges in pivot table can be problematic.

 

[Simon Lloyd]

Shane, 20 steps?, most of the registration info is purely to prevent bo
registration, anyway i would be pleased to complete the registration fo
you, simply email me at simonDOTlloyd@thecodecageDOTcom with you
preferred username and a valid email address that you want to use and
will mail you by return with your password which you can change afte
you sign in

Hi

I could not see your sample data. (The registration process su.., wen
through the 20 steps and then is rejected the input and cleared most o
th
fields. Extremely bad design, besides why do they need to know you
age
weight, social security number and visa?) A little exageration here

My suggestion is that you look at the command Data, Consolidate
Multipl
consolidation ranges in pivot table can be problematic


-
If this helps, please click the Yes button

Cheers
Shane Devenshir










(http://www.thecodecage.com/forumz/showthread.php?t=70423

--
Simon Lloy

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com