TheJonWithNoH
New Member
- Joined
- Sep 8, 2017
- Messages
- 30
I have a sheet (Sheet 2) with a start date in cell A1 and an end date in cell B1.
In another sheet (Sheet 1) I have a list of data, including dates in Column A and data in Columns B, C & D. Most of the dates in Column A are repetitive based on data value in Columns B, C & D.
I need to copy data from Sheet 1 to Sheet 2, including the repetitive dates, but only based on the date ranges on Sheet 2 in cells A1 & B1.
This new list of data on Sheet 2 should start in Column K through N and start in cell K3 through N3.
I have tried using the following formula in cell K1 which tells me how many instances are within the selected date range:
=COUNTIFS(DataDates,">="&StartSortDate,DataDates,"<="&EndSortDate)
I then use the following array formula starting in cell K3 & L3 which makes my workbook run VERY slow, but returns the correct dates & data in columns K & L, but columns M & N are numbers so this formula isn't working.
In K3 I have:{=IF(ROWS(K$3:K3)>K$1,"",TEXT(INDEX(Data1!A:A,SMALL(IF(Data1!A:A>=StartSortDate,IF(Data1!A:A<=EndSortDate,ROW(Data1!A:A))),ROWS(K$3:K3))),"m/d/yyyy"))}
In L3 I have:{=IF(ROWS(K$3:K3)>K$1,"",TEXT(INDEX(Data1!B:B,SMALL(IF(Data1!A:A>=StartSortDate,IF(Data1!A:A<=EndSortDate,ROW(Data1!A:A))),ROWS(K$3:K3))),""))}
How can I get the numbers from Sheet 1 over to Columns M & N? Also, is there a non-array formula that would be better? VBA code possibly?
Here's an example of my data on Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]10/1/2017[/TD]
[TD]RatePlan A[/TD]
[TD]1[/TD]
[TD]$150[/TD]
[/TR]
[TR]
[TD]10/1/2017[/TD]
[TD]RatePlan X[/TD]
[TD]5[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]10/2/2017[/TD]
[TD]RatePlan R[/TD]
[TD]3[/TD]
[TD]$350[/TD]
[/TR]
[TR]
[TD]10/2/2017[/TD]
[TD]RatePlan W[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]10/3/2017[/TD]
[TD]RatePlan Q[/TD]
[TD]3[/TD]
[TD]$300[/TD]
[/TR]
</tbody>[/TABLE]
Thank you in advance for your help!!!!
In another sheet (Sheet 1) I have a list of data, including dates in Column A and data in Columns B, C & D. Most of the dates in Column A are repetitive based on data value in Columns B, C & D.
I need to copy data from Sheet 1 to Sheet 2, including the repetitive dates, but only based on the date ranges on Sheet 2 in cells A1 & B1.
This new list of data on Sheet 2 should start in Column K through N and start in cell K3 through N3.
I have tried using the following formula in cell K1 which tells me how many instances are within the selected date range:
=COUNTIFS(DataDates,">="&StartSortDate,DataDates,"<="&EndSortDate)
I then use the following array formula starting in cell K3 & L3 which makes my workbook run VERY slow, but returns the correct dates & data in columns K & L, but columns M & N are numbers so this formula isn't working.
In K3 I have:{=IF(ROWS(K$3:K3)>K$1,"",TEXT(INDEX(Data1!A:A,SMALL(IF(Data1!A:A>=StartSortDate,IF(Data1!A:A<=EndSortDate,ROW(Data1!A:A))),ROWS(K$3:K3))),"m/d/yyyy"))}
In L3 I have:{=IF(ROWS(K$3:K3)>K$1,"",TEXT(INDEX(Data1!B:B,SMALL(IF(Data1!A:A>=StartSortDate,IF(Data1!A:A<=EndSortDate,ROW(Data1!A:A))),ROWS(K$3:K3))),""))}
How can I get the numbers from Sheet 1 over to Columns M & N? Also, is there a non-array formula that would be better? VBA code possibly?
Here's an example of my data on Sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]10/1/2017[/TD]
[TD]RatePlan A[/TD]
[TD]1[/TD]
[TD]$150[/TD]
[/TR]
[TR]
[TD]10/1/2017[/TD]
[TD]RatePlan X[/TD]
[TD]5[/TD]
[TD]$500[/TD]
[/TR]
[TR]
[TD]10/2/2017[/TD]
[TD]RatePlan R[/TD]
[TD]3[/TD]
[TD]$350[/TD]
[/TR]
[TR]
[TD]10/2/2017[/TD]
[TD]RatePlan W[/TD]
[TD]1[/TD]
[TD]$100[/TD]
[/TR]
[TR]
[TD]10/3/2017[/TD]
[TD]RatePlan Q[/TD]
[TD]3[/TD]
[TD]$300[/TD]
[/TR]
</tbody>[/TABLE]
Thank you in advance for your help!!!!