+ Reply to Thread
Results 1 to 3 of 3

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

  1. #1
    Registered User
    Join Date
    08-24-2014
    Location
    India
    MS-Off Ver
    2013
    Posts
    15

    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
    Attached Images Attached Images
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,076

    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.
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

  3. #3
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,076

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] SUM of Shift-wise and of both the Shifts
    By RavindraK in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-08-2018, 12:53 AM
  2. [SOLVED] Day and Shift wise allocation from Roster / employee shift schedule
    By Ravi_Kadu in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 04-24-2017, 10:57 AM
  3. [SOLVED] need to arrange column wise data to row wise with monthly wise
    By alok.gupta4ever in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 07-03-2016, 11:11 AM
  4. vba code for automating branch wise day wise amount to summary file
    By pranithpm in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-19-2014, 04:18 AM
  5. Excel code to represent data from row wise to column wise
    By sreejeshc in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 08-12-2013, 03:45 AM
  6. [SOLVED] I want change the data row wise to coloum wise & coloumn wise to row wise.
    By satputenandkumar0 in forum Excel General
    Replies: 3
    Last Post: 12-20-2012, 08:34 AM
  7. Replies: 4
    Last Post: 08-01-2012, 09:50 AM

Tags for this Thread

Bookmarks

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