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.

Please Login or Register  to view this content.

Expected result:

Capture.JPG

Mmm, the result seems to be the same?

(Which is good in this case)

Hey jindon,

I'm very new to VBA. I'm just doing some learning by doing and studying other's work. I haven't really followed a course yet, might I might soon for work.
This is only the 2nd attempt to make a tool so I was already happy to make some working loops. :p

Copying seems to be slower, but I like the result better.
Very nifty code and I made some additional changes to replace my code by this one.

Please Login or Register  to view this content.

2 questions:

1) Does this server a purpose? I guess not as I don't need the value 0 in column 3 and 4?

Please Login or Register  to view this content.

2) The result in ws2.cells(2, 4) for the date 3/07/2017 is "ZE-Zending".
When you look at ws1 you see that this date has 2 values "Var 7h36" and "ZE-Zending".
"ZE-Zending" is the right, but why does it take this one and not the other one?

Hey jindon,

I encountered a strange problem with Set c = rng2.Find(r.Value, , xlFormulas)
If r.value = 15/01/2016, it sets c = 15/11/2016 and thus starts copying the values for both 15/01/2016 and 15/11/2016.

E.g.

15/01/2016: it shows the 2 timestamps of 15/01/2016 and the 4 timestamps of 15/11/2016!

I highlighted some of these strange cases in red in wsSummary.

Would you mind having another look?

Please Login or Register  to view this content.

SOLVED Set c = rng2.Find(r.Value, , xlFormulas, xlWhole)