Results 1 to 13 of 13

Transpose vertical to horizontal data based on countif?

Threaded View

  1. #1
    Registered User
    Join Date
    07-04-2017
    Location
    Belgium
    MS-Off Ver
    365 ProPlus
    Posts
    81

    Transpose vertical to horizontal data based on countif?

    Hello all,

    I would like to transpose some vertical data (timestamps) in worksheet 3 horizontally to worksheet 2.

    Steps

    1. If you can't find the date from worksheet 2 column A in worksheet 3 column F, nothing should happen.
    2. If you can find the date, count the amount of times you find this date and copy the corresponding timestamps (to this date) to worksheet 2.
    E.g. 5/07/2017 has 2 timestamps in worksheet 3, thus worksheet 2 should only show a value in column Time1 and Time2.


    I don't really know how to loop through this or work with select case or so...

    Here below my flawed attempt, but the offset is a bit off and it doesn't copy the right amount of timestamps.

    lastRow = ws3.Cells(Rows.Count, "F").End(xlUp).Row
    Set r = ws3.Range("F2:F" & lastRow)
    
    i = 2
    
    Do While ws2.Cells(i, 1).Value <> ""
    
        For x = 1 To Application.WorksheetFunction.CountIf(ws3.Range("F:F"), ws2.Cells(i, 1))
    
        If r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues) Is Nothing Then
    
        ElseIf r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues) >= 1 Then
        
            ws2.Cells(i, 6).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x, 1)
            ws2.Cells(i, 11).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 1, 1)
            ws2.Cells(i, 16).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 2, 1)
            ws2.Cells(i, 21).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 3, 1)
            ws2.Cells(i, 26).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 4, 1)
            ws2.Cells(i, 31).Value = r.Find(What:=ws2.Cells(i, 1), LookIn:=xlValues).Offset(x + 5, 1)
        
        End If
    
    Next x
    
    i = i + 1
    
    Loop
    Expected result:

    Capture.JPG
    Attached Files Attached Files
    Last edited by dunnobe; 01-24-2018 at 01:55 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Transpose huge data from horizontal to vertical
    By AaruJaan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-23-2017, 12:13 PM
  2. [SOLVED] Transpose data from horizontal to vertical in specific column
    By YasserKhalil in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-29-2016, 12:39 AM
  3. [SOLVED] Transpose vertical data into horizontal records vba
    By nolans18 in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-27-2014, 08:50 PM
  4. [SOLVED] Data transpose (from vertical input to Horizontal output)
    By nur2544 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-22-2013, 11:12 AM
  5. Transpose Vertical Data to Horizontal
    By Randu555 in forum Excel General
    Replies: 5
    Last Post: 04-18-2013, 05:05 PM
  6. Replies: 1
    Last Post: 10-03-2012, 02:46 PM
  7. [SOLVED] transpose data from horizontal to vertical in a specific column
    By elaine in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 06-08-2006, 12:10 PM

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