Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Transposing multiple rows to multiple columns

Hello,

I have a very large amount of data (workday time punch data) that I need to
alter from multiple rows to multiple columns on one row. See small example
of data as follows where FirstName, LastName, Badge, Store, ScanDttm, RAW,
CONVERTED are column headings (A1 thru G1):

FirstName LastName Badge Store ScanDttm RAW CONVERTED
ROBERT H. CASAVAN 600265 1211 1/2/2004 28:00.0 9:28:00 AM
ROBERT H. CASAVAN 600265 1211 1/2/2004 57:00.0 2:57:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 24:00.0 3:24:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 03:00.0 5:03:00 PM
BRIAN BAUM 600760 1211 1/2/2004 36:00.0 9:36:00 AM
BRIAN BAUM 600760 1211 1/2/2004 05:00.0 3:05:00 PM
MICHAEL ANTOSIK 600820 1439 1/2/2004 52:00.0 9:52:00 AM
MICHAEL ANTOSIK 600820 1439 1/2/2004 06:00.0 1:06:00 PM

I want to take the CONVERTED data (time punch in & out time) for ROBERT H.
CASAVAN on 1/2/04 (ScanDttm) and spread those 4 in & out time entries into 1
row and multiple columns. Then the same for BRIAN BAUM, and the same for
MICHAEL ANTOSIK, etc. Each individual will have from 1 to 4 in & out entries
on a given day.

Is there a way to accomplish this without copying and transposing each set
of in & out time entries for each person and each day separately? If so, how
do I do that?

Thank you!
--
heyredone
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,344
Default Transposing multiple rows to multiple columns

Hi,

You can transpose data using either the =TRANSPOSE(A1:A4) function or using
the copy Paste Special Transpose command.

You may want to write a macro to do one or the other of these. But first
test to see which works for you, then record a macro to convert one row of
data and post it as a new question and ask for help generalizing it to n rows
of data.

For the Transpose function you highlight four cells, type the formula and
press Shift Ctrl Enter, to enter it

--
Thanks,
Shane Devenshire


"heyredone" wrote:

Hello,

I have a very large amount of data (workday time punch data) that I need to
alter from multiple rows to multiple columns on one row. See small example
of data as follows where FirstName, LastName, Badge, Store, ScanDttm, RAW,
CONVERTED are column headings (A1 thru G1):

FirstName LastName Badge Store ScanDttm RAW CONVERTED
ROBERT H. CASAVAN 600265 1211 1/2/2004 28:00.0 9:28:00 AM
ROBERT H. CASAVAN 600265 1211 1/2/2004 57:00.0 2:57:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 24:00.0 3:24:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 03:00.0 5:03:00 PM
BRIAN BAUM 600760 1211 1/2/2004 36:00.0 9:36:00 AM
BRIAN BAUM 600760 1211 1/2/2004 05:00.0 3:05:00 PM
MICHAEL ANTOSIK 600820 1439 1/2/2004 52:00.0 9:52:00 AM
MICHAEL ANTOSIK 600820 1439 1/2/2004 06:00.0 1:06:00 PM

I want to take the CONVERTED data (time punch in & out time) for ROBERT H.
CASAVAN on 1/2/04 (ScanDttm) and spread those 4 in & out time entries into 1
row and multiple columns. Then the same for BRIAN BAUM, and the same for
MICHAEL ANTOSIK, etc. Each individual will have from 1 to 4 in & out entries
on a given day.

Is there a way to accomplish this without copying and transposing each set
of in & out time entries for each person and each day separately? If so, how
do I do that?

Thank you!
--
heyredone

  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Transposing multiple rows to multiple columns

heyredone wrote:
Hello,

I have a very large amount of data (workday time punch data) that I need to
alter from multiple rows to multiple columns on one row. See small example
of data as follows where FirstName, LastName, Badge, Store, ScanDttm, RAW,
CONVERTED are column headings (A1 thru G1):

FirstName LastName Badge Store ScanDttm RAW CONVERTED
ROBERT H. CASAVAN 600265 1211 1/2/2004 28:00.0 9:28:00 AM
ROBERT H. CASAVAN 600265 1211 1/2/2004 57:00.0 2:57:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 24:00.0 3:24:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 03:00.0 5:03:00 PM
BRIAN BAUM 600760 1211 1/2/2004 36:00.0 9:36:00 AM
BRIAN BAUM 600760 1211 1/2/2004 05:00.0 3:05:00 PM
MICHAEL ANTOSIK 600820 1439 1/2/2004 52:00.0 9:52:00 AM
MICHAEL ANTOSIK 600820 1439 1/2/2004 06:00.0 1:06:00 PM

I want to take the CONVERTED data (time punch in & out time) for ROBERT H.
CASAVAN on 1/2/04 (ScanDttm) and spread those 4 in & out time entries into 1
row and multiple columns. Then the same for BRIAN BAUM, and the same for
MICHAEL ANTOSIK, etc. Each individual will have from 1 to 4 in & out entries
on a given day.

Is there a way to accomplish this without copying and transposing each set
of in & out time entries for each person and each day separately? If so, how
do I do that?

Thank you!



Array enter (CTRL+SHIFT+ENTER) the following in H2 (adjusting the 9999 to the
correct number of rows in your data):

=IF(AND(COUNTIF($C$2:$C2,$C2)=1,COUNTIF($C$2:$C$99 99,$C2)COLUMN()-8),
LARGE(TRANSPOSE(($C$2:$C$9999=$C2)*$G$2:$G$9999),8 +COUNTIF($C$2:$C$9999,$C2)-COLUMN()),"")

Copy across to K2 and down as needed.
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Transposing multiple rows to multiple columns

Glenn wrote:
heyredone wrote:
Hello,

I have a very large amount of data (workday time punch data) that I
need to alter from multiple rows to multiple columns on one row. See
small example of data as follows where FirstName, LastName, Badge,
Store, ScanDttm, RAW, CONVERTED are column headings (A1 thru G1):

FirstName LastName Badge Store ScanDttm RAW CONVERTED
ROBERT H. CASAVAN 600265 1211 1/2/2004 28:00.0
9:28:00 AM
ROBERT H. CASAVAN 600265 1211 1/2/2004 57:00.0
2:57:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 24:00.0
3:24:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 03:00.0
5:03:00 PM
BRIAN BAUM 600760 1211 1/2/2004 36:00.0 9:36:00 AM
BRIAN BAUM 600760 1211 1/2/2004 05:00.0 3:05:00 PM
MICHAEL ANTOSIK 600820 1439 1/2/2004 52:00.0 9:52:00 AM
MICHAEL ANTOSIK 600820 1439 1/2/2004 06:00.0 1:06:00 PM

I want to take the CONVERTED data (time punch in & out time) for
ROBERT H. CASAVAN on 1/2/04 (ScanDttm) and spread those 4 in & out
time entries into 1 row and multiple columns. Then the same for BRIAN
BAUM, and the same for MICHAEL ANTOSIK, etc. Each individual will
have from 1 to 4 in & out entries on a given day.

Is there a way to accomplish this without copying and transposing each
set of in & out time entries for each person and each day separately?
If so, how do I do that?

Thank you!



Array enter (CTRL+SHIFT+ENTER) the following in H2 (adjusting the 9999
to the correct number of rows in your data):

=IF(AND(COUNTIF($C$2:$C2,$C2)=1,COUNTIF($C$2:$C$99 99,$C2)COLUMN()-8),
LARGE(TRANSPOSE(($C$2:$C$9999=$C2)*$G$2:$G$9999),8 +COUNTIF($C$2:$C$9999,$C2)-COLUMN()),"")


Copy across to K2 and down as needed.



Looks like the TRANSPOSE wasn't needed:

=IF(AND(COUNTIF($C$2:$C2,$C2)=1,COUNTIF($C$2:$C$99 9,$C2)COLUMN()-8),
LARGE(($C$2:$C$999=$C2)*$G$2:$G$999,8+COUNTIF($C$2 :$C$999,$C2)-COLUMN()),"")
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Transposing multiple rows to multiple columns

Glenn,
Thank you for your quick response. Okay, I tried the array string you
provided. I get $NUM in K2. What did I do wrong? Also, why am I entering
$C$2 or $C2 ... is that the cell C2? My little chart of data may not have
looked exactly right -- the in & out time punches are located starting in G2
and downward. Just trying to understand.

Thanks so much!
--
heyredone


"Glenn" wrote:

Glenn wrote:
heyredone wrote:
Hello,

I have a very large amount of data (workday time punch data) that I
need to alter from multiple rows to multiple columns on one row. See
small example of data as follows where FirstName, LastName, Badge,
Store, ScanDttm, RAW, CONVERTED are column headings (A1 thru G1):

FirstName LastName Badge Store ScanDttm RAW CONVERTED
ROBERT H. CASAVAN 600265 1211 1/2/2004 28:00.0
9:28:00 AM
ROBERT H. CASAVAN 600265 1211 1/2/2004 57:00.0
2:57:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 24:00.0
3:24:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 03:00.0
5:03:00 PM
BRIAN BAUM 600760 1211 1/2/2004 36:00.0 9:36:00 AM
BRIAN BAUM 600760 1211 1/2/2004 05:00.0 3:05:00 PM
MICHAEL ANTOSIK 600820 1439 1/2/2004 52:00.0 9:52:00 AM
MICHAEL ANTOSIK 600820 1439 1/2/2004 06:00.0 1:06:00 PM

I want to take the CONVERTED data (time punch in & out time) for
ROBERT H. CASAVAN on 1/2/04 (ScanDttm) and spread those 4 in & out
time entries into 1 row and multiple columns. Then the same for BRIAN
BAUM, and the same for MICHAEL ANTOSIK, etc. Each individual will
have from 1 to 4 in & out entries on a given day.

Is there a way to accomplish this without copying and transposing each
set of in & out time entries for each person and each day separately?
If so, how do I do that?

Thank you!



Array enter (CTRL+SHIFT+ENTER) the following in H2 (adjusting the 9999
to the correct number of rows in your data):

=IF(AND(COUNTIF($C$2:$C2,$C2)=1,COUNTIF($C$2:$C$99 99,$C2)COLUMN()-8),
LARGE(TRANSPOSE(($C$2:$C$9999=$C2)*$G$2:$G$9999),8 +COUNTIF($C$2:$C$9999,$C2)-COLUMN()),"")


Copy across to K2 and down as needed.



Looks like the TRANSPOSE wasn't needed:

=IF(AND(COUNTIF($C$2:$C2,$C2)=1,COUNTIF($C$2:$C$99 9,$C2)COLUMN()-8),
LARGE(($C$2:$C$999=$C2)*$G$2:$G$999,8+COUNTIF($C$2 :$C$999,$C2)-COLUMN()),"")



  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Transposing multiple rows to multiple columns

heyredone wrote:
Glenn,
Thank you for your quick response. Okay, I tried the array string you
provided. I get $NUM in K2. What did I do wrong? Also, why am I entering
$C$2 or $C2 ... is that the cell C2? My little chart of data may not have
looked exactly right -- the in & out time punches are located starting in G2
and downward. Just trying to understand.

Thanks so much!


Not sure why you would get $NUM. The formula will expand to the right as far as
you want. If there is text in the CONVERTED column you get #VALUE!. If you use
exactly the data in your original post, what results do you get in H2:K2?

Yes, I am referencing column C in the formula. I assumed that the Badge number
would be unique for each employee. If that's not right, let me know.

Also, I forgot to include the date, assuming the same badge number will be in
your data for more than one date. Correction as follows (array-entered):

=IF(AND(SUMPRODUCT(($C$2:$C2=$C2)*($E$2:$E2=$E2))= 1,
SUMPRODUCT(($C$2:$C999=$C2)*($E$2:$E999=$E2))COLU MN()-8),
LARGE(($C$2:$C$999=$C2)*($E$2:$E$999=$E2)*$G$2:$G$ 999,
8+SUMPRODUCT(($C$2:$C999=$C2)*($E$2:$E999=$E2))-COLUMN()),"")

I have data starting in row 2 with column headings in row 1 as follows:

A - FirstName
B - LastName
C - Badge
D - Store
E - ScanDttm
F - RAW
G - CONVERTED

Columns H, I, J and K should be the new columns for your punch times. If that's
not right, let me know and I'll adjust the formula.
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Transposing multiple rows to multiple columns

Glenn,

Sorry for the delay in responding. Just as I was attempting to get back to
work in the spreadsheet yesterday to include the new array you provided, I
encountered technical difficulties and have not been able to get back into
the document safely. As soon as the issue is resolved, I will try it and let
you know how it turns out.

Thanks!
--
heyredone


"Glenn" wrote:

heyredone wrote:
Glenn,
Thank you for your quick response. Okay, I tried the array string you
provided. I get $NUM in K2. What did I do wrong? Also, why am I entering
$C$2 or $C2 ... is that the cell C2? My little chart of data may not have
looked exactly right -- the in & out time punches are located starting in G2
and downward. Just trying to understand.

Thanks so much!


Not sure why you would get $NUM. The formula will expand to the right as far as
you want. If there is text in the CONVERTED column you get #VALUE!. If you use
exactly the data in your original post, what results do you get in H2:K2?

Yes, I am referencing column C in the formula. I assumed that the Badge number
would be unique for each employee. If that's not right, let me know.

Also, I forgot to include the date, assuming the same badge number will be in
your data for more than one date. Correction as follows (array-entered):

=IF(AND(SUMPRODUCT(($C$2:$C2=$C2)*($E$2:$E2=$E2))= 1,
SUMPRODUCT(($C$2:$C999=$C2)*($E$2:$E999=$E2))COLU MN()-8),
LARGE(($C$2:$C$999=$C2)*($E$2:$E$999=$E2)*$G$2:$G$ 999,
8+SUMPRODUCT(($C$2:$C999=$C2)*($E$2:$E999=$E2))-COLUMN()),"")

I have data starting in row 2 with column headings in row 1 as follows:

A - FirstName
B - LastName
C - Badge
D - Store
E - ScanDttm
F - RAW
G - CONVERTED

Columns H, I, J and K should be the new columns for your punch times. If that's
not right, let me know and I'll adjust the formula.

  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Transposing multiple rows to multiple columns

Glenn,

I was finally able to get back into the document this morning after
addressing the technical issues. I tried the new array you provided me on
your last post -- worked perfectly! Yeah! Thank you so much!

I work quite often on these types of spreadsheets containing time punches
and hours worked data so this array will be great to use when faced with a
similar format of information.

In fact, I have yet another type of spreadsheet of data that I need to work
on that I may need some additional Excel help on. I will post query to the
General Discussion group unless you have the time and/or interest in helping
me with it.

Thank you very much!
--
heyredone


"heyredone" wrote:

Glenn,

Sorry for the delay in responding. Just as I was attempting to get back to
work in the spreadsheet yesterday to include the new array you provided, I
encountered technical difficulties and have not been able to get back into
the document safely. As soon as the issue is resolved, I will try it and let
you know how it turns out.

Thanks!
--
heyredone


"Glenn" wrote:

heyredone wrote:
Glenn,
Thank you for your quick response. Okay, I tried the array string you
provided. I get $NUM in K2. What did I do wrong? Also, why am I entering
$C$2 or $C2 ... is that the cell C2? My little chart of data may not have
looked exactly right -- the in & out time punches are located starting in G2
and downward. Just trying to understand.

Thanks so much!


Not sure why you would get $NUM. The formula will expand to the right as far as
you want. If there is text in the CONVERTED column you get #VALUE!. If you use
exactly the data in your original post, what results do you get in H2:K2?

Yes, I am referencing column C in the formula. I assumed that the Badge number
would be unique for each employee. If that's not right, let me know.

Also, I forgot to include the date, assuming the same badge number will be in
your data for more than one date. Correction as follows (array-entered):

=IF(AND(SUMPRODUCT(($C$2:$C2=$C2)*($E$2:$E2=$E2))= 1,
SUMPRODUCT(($C$2:$C999=$C2)*($E$2:$E999=$E2))COLU MN()-8),
LARGE(($C$2:$C$999=$C2)*($E$2:$E$999=$E2)*$G$2:$G$ 999,
8+SUMPRODUCT(($C$2:$C999=$C2)*($E$2:$E999=$E2))-COLUMN()),"")

I have data starting in row 2 with column headings in row 1 as follows:

A - FirstName
B - LastName
C - Badge
D - Store
E - ScanDttm
F - RAW
G - CONVERTED

Columns H, I, J and K should be the new columns for your punch times. If that's
not right, let me know and I'll adjust the formula.

  #9   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Transposing multiple rows to multiple columns

Shane,
Thank you for your response. As you can see from the e-mail string, I did
use Glenn's array suggestion and it worked wonderfully.
Thanks again for responding.
--
heyredone


"ShaneDevenshire" wrote:

Hi,

You can transpose data using either the =TRANSPOSE(A1:A4) function or using
the copy Paste Special Transpose command.

You may want to write a macro to do one or the other of these. But first
test to see which works for you, then record a macro to convert one row of
data and post it as a new question and ask for help generalizing it to n rows
of data.

For the Transpose function you highlight four cells, type the formula and
press Shift Ctrl Enter, to enter it

--
Thanks,
Shane Devenshire


"heyredone" wrote:

Hello,

I have a very large amount of data (workday time punch data) that I need to
alter from multiple rows to multiple columns on one row. See small example
of data as follows where FirstName, LastName, Badge, Store, ScanDttm, RAW,
CONVERTED are column headings (A1 thru G1):

FirstName LastName Badge Store ScanDttm RAW CONVERTED
ROBERT H. CASAVAN 600265 1211 1/2/2004 28:00.0 9:28:00 AM
ROBERT H. CASAVAN 600265 1211 1/2/2004 57:00.0 2:57:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 24:00.0 3:24:00 PM
ROBERT H. CASAVAN 600265 1211 1/2/2004 03:00.0 5:03:00 PM
BRIAN BAUM 600760 1211 1/2/2004 36:00.0 9:36:00 AM
BRIAN BAUM 600760 1211 1/2/2004 05:00.0 3:05:00 PM
MICHAEL ANTOSIK 600820 1439 1/2/2004 52:00.0 9:52:00 AM
MICHAEL ANTOSIK 600820 1439 1/2/2004 06:00.0 1:06:00 PM

I want to take the CONVERTED data (time punch in & out time) for ROBERT H.
CASAVAN on 1/2/04 (ScanDttm) and spread those 4 in & out time entries into 1
row and multiple columns. Then the same for BRIAN BAUM, and the same for
MICHAEL ANTOSIK, etc. Each individual will have from 1 to 4 in & out entries
on a given day.

Is there a way to accomplish this without copying and transposing each set
of in & out time entries for each person and each day separately? If so, how
do I do that?

Thank you!
--
heyredone

  #10   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,240
Default Transposing multiple rows to multiple columns

heyredone wrote:
Glenn,

I was finally able to get back into the document this morning after
addressing the technical issues. I tried the new array you provided me on
your last post -- worked perfectly! Yeah! Thank you so much!

I work quite often on these types of spreadsheets containing time punches
and hours worked data so this array will be great to use when faced with a
similar format of information.

In fact, I have yet another type of spreadsheet of data that I need to work
on that I may need some additional Excel help on. I will post query to the
General Discussion group unless you have the time and/or interest in helping
me with it.

Thank you very much!



Glad I could be of assistance. Please post further questions to the newsgroup.
I will look at them if I have time, but if not, someone else surely will help
you.


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 24
Default Transposing multiple rows to multiple columns

Thanks again. I will post to Discussion group.
--
heyredone


"Glenn" wrote:

heyredone wrote:
Glenn,

I was finally able to get back into the document this morning after
addressing the technical issues. I tried the new array you provided me on
your last post -- worked perfectly! Yeah! Thank you so much!

I work quite often on these types of spreadsheets containing time punches
and hours worked data so this array will be great to use when faced with a
similar format of information.

In fact, I have yet another type of spreadsheet of data that I need to work
on that I may need some additional Excel help on. I will post query to the
General Discussion group unless you have the time and/or interest in helping
me with it.

Thank you very much!



Glad I could be of assistance. Please post further questions to the newsgroup.
I will look at them if I have time, but if not, someone else surely will help
you.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Transposing multiple columns to rows Pittman Excel Worksheet Functions 5 September 8th 08 01:47 PM
Transposing One Column to Multiple Rows Jose Excel Worksheet Functions 1 August 14th 08 09:26 PM
Transposing Multiple Cell References to Multiple Values (NOT total LinLin Excel Discussion (Misc queries) 7 November 11th 07 11:57 PM
Transposing Multiple Cell references as Multiple Values LinLin Excel Discussion (Misc queries) 1 November 8th 07 02:21 AM
transposing data from 1 column into multiple rows Gina Excel Discussion (Misc queries) 2 April 5th 07 06:06 PM


All times are GMT +1. The time now is 10:58 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"