# Code & date fix But Time shift row to Column wise...

1. ## Code & date fix But Time shift row to Column wise...

Dear all
I have sum raw data in sheet1

Can u please suggest me any procedure to transform all data as per sheet2

Time is row wise as per code & date, but I want to time fill Column wise as per code & date.

Sorry for my bad English

Thanks & regards
Suman Chowdhury

2. ## Re: Code & date fix But Time shift row to Column wise...

The key is to assign a time slot to each entry: =COUNTIF(\$A\$2:\$A\$2000,A2)-COUNTIF(A2:\$A\$2000,A2)+1

The COUNTIF(A2:\$A\$2000,A2) part uses relative addressing to get the number of times the value in column A appears in the current row or below. By itself this would create the time slots in inverse order 45, 44, 43 ... So we subtract it from the total number of times the value in column A appears (COUNTIF(\$A\$2:\$A\$2000,A2) - note the absolute addressing). This starts the count at zero, so add 1.

Then make a pivot table out of this with Column A as the row header and time slot as the column header and the time itself as the value.

3. ## Re: Code & date fix But Time shift row to Column wise...

I just noticed that you want the display grouped by Code and date, so first we have to make a "composite key." =A2&":"&B2. Then assign a time slot as described above except do it on column D =COUNTIF(\$D\$2:\$D\$2000,D2)-COUNTIF(D2:\$D\$2000,D2)+1.

Then make the pivot table.

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1