Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Index, Match, and Transpose

0

I Love this site! A ton of resources and information.

Colum A is the database for the start and end time. Col B is the price and it changes every second. Colum C and D are pulled from another sheet when an order was placed start time and when the order close the end time.

My scenario is to track price changes, and reflect changes transpose Col B price to Col E, F, G..

Col A list Date/Time, Col B list price, Col C list the start Date/Time, Col D list the end Date/Time. The Start and End dates are matched in Col A.

I pulled all these 6-AWESOME formulas/arrays from this site. My limited knowledge has made it difficult to connect the dots between the Start and End Date to transpose.

Example: I must Match C2 and D2 against Col A, and transpose all the prices Col B between those 2 dates into Col E, G, H...   

=INDEX(A:A, MATCH(TRUE, INDEX(A:A=C2, 0), 0))
=INDEX(A:A,SUMPRODUCT(--(C2=A:A),ROW(C:C)))
=TRANSPOSE(INDEX(A:A,MATCH(C2,A:A,0),0))
=SUM(IF(FREQUENCY(IF(A:A=C2,C:C),IF(A:A=D2,C:C))>0,1))
=SUMIF(A:A,C2,C:C)
=INDEX(INDEX(A:A,IF(ROW(A2)=1,1,SMALL(IF(ISNUMBER(SEARCH(C2,A:A)),
ROW(A:A),10^100),ROW(A2)-1)+1)):A,COLUMNS($A2:A2)) 

Col A     
Date/TIME
3/28/2016 1:00
3/28/2016 1:01
3/28/2016 1:02
3/28/2016 1:03
3/28/2016 1:05
3/28/2016 1:06
3/28/2016 1:07
3/28/2016 1:08


Col B
PRICE
17567
17566
17565
17565
17563
17561
17560
17561

Col C
START
3/28/2016 1:00
3/28/2016 1:02

Col D
END
3/28/2016 1:03
3/28/2016 1:08

End Result:


       
         
Col C         Col D                        Col E   Col F  Col G  Col H  Col I  Col J
START         END      
3/28/2016_1:00 3/28/2016 1:00 17567 17566 17565   
3/28/2016_1:02 3/28/2016 1:01 17566 17565 1756 17561 17560 17561

I have attached the above  xls example

excelformulas-functionsvlookupconsolidate-datatext-manipulation
MS Office Version: 2010
posted by: LMT on Sat Jul 16, 2016 at 11:40 am
Answer
Discuss

Discussion

Welcome! :) I am looking at your file and your question and I just don't understand what you are trying to do.

In the "End Result" section you have 17567, 17566, and 17565 for row 15 but I'm not sure where you are supposed to have gotten those numbers and how.

Please update your question with a simpler example and explanation. Walk me through what you are trying to do an I can help
don (rep: 1989) Jul 16, '16 at 11:49 am
Let me know if my answer worked for you.
don (rep: 1989) Jul 16, '16 at 2:07 pm
Add to Discussion

Answers

0

You have to realize that times are a bitch when doing lookups due to rounding issues.

That said, This is the formula that you need:

=IF(COLUMN()-4 <= MINUTE($D15-$C15),VLOOKUP(ROUND($C15+TIME(0,COLUMN()-5,0),5),$A$15:$B$22,2),"")

I tested it on the Results section. Put it into column E, row 15 and copy it to the right and down and it should work.

BUT, there is one more thing you should do to get accurate results.

For your time column, create a new temporary column next to it and type:

=ROUND(B15,5)

Put that into cell A15 (the new temporary column should be column A to the left of the dates). Copy the formula down for all the dates.

Select all of the new formulas and hit Ctrl + C and then select all of the old dates and hit Alt + E + S + V Enter. This will copy/paste values.

Now you will have a new list of dates that should look the same as the old one. Delete the temporary column that has the formulas and you should be good to go.

posted by: don (rep: 1989) on Sat Jul 16, 2016 at 12:51 pm
Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login