aligning data
 

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

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

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

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

i need to keep the individual data sets, so i can't just count up all
the individual codes and give the total. i've attached a couple samples
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 anyone
point me in the right direction? i don't mind if this will take a bit
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|
+-------------------------------------------------------------------+

--
carl669
------------------------------------------------------------------------
carl669's Profile: http://www.thecodecage.com/forumz/member.php?userid=158
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=70423

aligning data
 

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

carl669;252167 Wrote:
never really needed to use excel in the past, so apologies if this is a
dumb question.

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

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

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

i need to keep the individual data sets, so i can't just count up all
the individual codes and give the total. i've attached a couple samples
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 anyone
point me in the right direction? i don't mind if this will take a bit of
work because it will save my group a LOT of time down the road.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=70423

aligning data
 

i don't really like the end format either, but there is a reason to the
madness. basically, we need to keep the data separate to help track
down 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 a particular code looks high or low, and then have the client
go back fix any data errors we find.


Simon Lloyd;252186 Wrote:
Welcome to The Code Cage!

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


--
carl669
------------------------------------------------------------------------
carl669's Profile: http://www.thecodecage.com/forumz/member.php?userid=158
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=70423

aligning data
 

carl669;252211 Wrote:
i don't really like the end format either, but there is a reason to the
madness. basically, we need to keep the data separate to help track down
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 a
particular code looks high or low, and then have the client go back fix
any data errors we find.



--- Automerged consecutive post before response ---
well...it turns out that we do need the overall total for each code as
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 all
4 sets of data.

i end up with something like this:
Code/RecCnt/Grandtotal
HMCM 21 21
HMCM 7 7
(in the individual data, HMCM is in all 4 columns with counts of
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 and
further help tomorrow.


--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=70423

aligning data
 

thanks for the help.

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


--
carl669
------------------------------------------------------------------------
carl669's Profile: http://www.thecodecage.com/forumz/member.php?userid=158
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=70423

aligning data
 

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.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"carl669" wrote:


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

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

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

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

i need to keep the individual data sets, so i can't just count up all
the individual codes and give the total. i've attached a couple samples
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 anyone
point me in the right direction? i don't mind if this will take a bit
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|
+-------------------------------------------------------------------+

--
carl669
------------------------------------------------------------------------
carl669's Profile: http://www.thecodecage.com/forumz/member.php?userid=158
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=70423

aligning data
 

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

Shane Devenshire;252916 Wrote:
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.


--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


"carl669" wrote:


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

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

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

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

i need to keep the individual data sets, so i can't just count up all
the individual codes and give the total. i've attached a couple
samples
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 anyone
point me in the right direction? i don't mind if this will take a bit
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|
+-------------------------------------------------------------------+

--
carl669

------------------------------------------------------------------------
carl669's Profile: 'The Code Cage Forums - View Profile: carl669'
(http://www.thecodecage.com/forumz/member.php?userid=158)
View this thread: 'aligning data - The Code Cage Forums'
(http://www.thecodecage.com/forumz/sh...ad.php?t=70423)




--
Simon Lloyd

Regards,
Simon Lloyd
'The Code Cage' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=70423