Sort 2 database separately by date and adding space in between dates that are different

B

bobbieatendido

New Member
Joined
Jul 13, 2016
Messages
21
Office Version
  1. 365
Platform
  1. Windows
Hi,

Is there a way to automatically sort 2 database's by date and add space in between dates that are different?

Book1.xlsx
ABCDE
1Office 1
2DatePO#JO#FROM-TOPRICE
310/30/2323554510001HMD-TOI34,234
411/04/2323445610023FSK-IFM23,423
511/08/2323454310033KFM-FKS232
611/08/2323432510034DIC-KKO2,456
711/10/2323552410056SDK-OSD6,545
811/10/2334632410006OSD-SDK456
911/11/2335678410045DAN-CEW5,434
1011/15/2337894010046MNL-KLW6,645
1111/17/2339846710076PHP-USD4,564
1211/20/2325554510044JDL-JKD7,889
Sheet1


Book1.xlsx
GHIJK
1Office 2
2DatePO#JO#FROM-TOPRICE
310/30/2323445610056SDK-OSD5,434
411/04/2323454310006OSD-SDK6,645
511/05/2323432510045DAN-CEW4,564
611/08/2323552410025SDK-OSD4,678
711/10/2323545910019OSD-SDK4,243
811/10/2323575710014SDK-OSD3,808
911/11/2323605610008OSD-SDK3,373
1011/16/2323635410003FSK-IFM2,938
1111/17/2323665310025KFM-FKS2,503
1211/20/2323695210019PHP-USD2,068
Sheet1


Book1.xlsx
ABCDEFGHIJK
14OFFICE 1&2
15DatePO#JO#FROM-TOPRICEDatePO#JO#FROM-TOPRICE
1610/30/2323554510001HMD-TOI34,23410/30/2323445610056SDK-OSD5,434
1711/04/2323445610023FSK-IFM23,42311/04/2323454310006OSD-SDK6,645
1811/05/2323432510045DAN-CEW4,564
1911/08/2323454310033KFM-FKS23211/08/2323552410025SDK-OSD4,678
2011/08/2323432510034DIC-KKO2,456
2111/10/2323552410056SDK-OSD6,54511/10/2323545910019OSD-SDK4,243
2211/10/2334632410006OSD-SDK45611/10/2323575710014SDK-OSD3,808
2311/11/2335678410045DAN-CEW5,43411/11/2323605610008OSD-SDK3,373
2411/15/2337894010046MNL-KLW6,645
2511/16/2323635410003FSK-IFM2,938
2611/17/2339846710076PHP-USD4,56411/17/2323665310025KFM-FKS2,503
2711/20/2325554510044JDL-JKD7,88911/20/2323695210019PHP-USD2,068
Sheet1


In office 1 there is no date 11/05/23 and 11/16/23, so it will add a row to accommodate the date from office 2.
Same with office 2, since there is only 11/08/23, it will add a row so 11/10/23 in office 1 and 2 will be in one row.

Thank you
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Sort by date Sort by votes
It was a little more complicated than expected. Or at least I didn't find any simpler solution. Here it is:
Both are tables named Office1 and Office2:

TablasConEspacios.xlsx
BCDEFGHIJKLM
2Office 1Office 2
3DatePO#JO#FROM-TOPRICEDatePO#JO#FROM-TOPRICE
430/10/202323554510001HMD-TOI3423430/10/202323445610056SDK-OSD5434
504/11/202323445610023FSK-IFM2342304/11/202323454310006OSD-SDK6645
608/11/202323454310033KFM-FKS23205/11/202323432510045DAN-CEW4564
708/11/202323432510034DIC-KKO245608/11/202323552410024.6667SDK-OSD4677.66667
810/11/202323552410056SDK-OSD654510/11/2023235458.510019.1667OSD-SDK4242.66667
910/11/202334632410006OSD-SDK45610/11/2023235757.110013.6667SDK-OSD3807.66667
1011/11/202335678410045DAN-CEW543411/11/2023236055.710008.1667OSD-SDK3372.66667
1115/11/202337894010046MNL-KLW664516/11/2023236354.310002.6667FSK-IFM2937.66667
1217/11/202339846710076PHP-USD456417/11/2023236652.910024.6667KFM-FKS2502.66667
1320/11/202325554510044JDL-JKD788920/11/2023236951.510019.1667PHP-USD2067.66667
14
15
16DatePO#JO#FROM-TOPRICEDatePO#JO#FROM-TOPRICE
1730/10/202323554510001HMD-TOI3423430/10/202323445610056SDK-OSD5434
1804/11/202323445610023FSK-IFM2342304/11/202323454310006OSD-SDK6645
1905/11/202323432510045DAN-CEW4564
2008/11/202323454310033KFM-FKS23208/11/202323552410024.6667SDK-OSD4677.66667
2108/11/202323432510034DIC-KKO2456
2210/11/202323552410056SDK-OSD654510/11/2023235458.510019.1667OSD-SDK4242.66667
2310/11/202334632410006OSD-SDK45610/11/2023235757.110013.6667SDK-OSD3807.66667
2411/11/202335678410045DAN-CEW543411/11/2023236055.710008.1667OSD-SDK3372.66667
2515/11/202337894010046MNL-KLW6645
2616/11/2023236354.310002.6667FSK-IFM2937.66667
2717/11/202339846710076PHP-USD456417/11/2023236652.910024.6667KFM-FKS2502.66667
2820/11/202325554510044JDL-JKD788920/11/2023236951.510019.1667PHP-USD2067.66667
Hoja1
Cell Formulas
RangeFormula
B16:K28B16=LET( O1Dates,Office1[Date], O1DatesUnicos,HSTACK(UNIQUE(O1Dates),BYROW(UNIQUE(O1Dates), LAMBDA(x, SUM((x=O1Dates)*1)))), Frequency1, CHOOSECOLS(O1DatesUnicos,2), Index1,REDUCE(,Frequency1,LAMBDA(x,y,VSTACK(x,SEQUENCE(y)))), DatePIndex1, O1Dates+Index1/10000, O2Dates,Office2[Date], O2DatesUnicos,HSTACK(UNIQUE(O2Dates),BYROW(UNIQUE(O2Dates), LAMBDA(x, SUM((x=O2Dates)*1)))), Frequency2, CHOOSECOLS(O2DatesUnicos,2), Index2,REDUCE(,Frequency2,LAMBDA(x,y,VSTACK(x,SEQUENCE(y)))), DatePIndex2,O2Dates+Index2/10000, AllDatesIndex,SORT(UNIQUE(VSTACK(DatePIndex1,DatePIndex2))), Of1WIndex,HSTACK(DatePIndex1,Office1), Of2WIndex,HSTACK(DatePIndex2,Office2), Of1Result, HSTACK(MAP(AllDatesIndex,LAMBDA(x,INDEX(Office1,MATCH(x,DatePIndex1,0),1))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Office1,MATCH(x,DatePIndex1,0),2))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Office1,MATCH(x,DatePIndex1,0),3))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Office1,MATCH(x,DatePIndex1,0),4))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Office1,MATCH(x,DatePIndex1,0),5)))), Of2Result, HSTACK(MAP(AllDatesIndex,LAMBDA(x,INDEX(Office2,MATCH(x,DatePIndex2,0),1))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Office2,MATCH(x,DatePIndex2,0),2))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Office2,MATCH(x,DatePIndex2,0),3))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Office2,MATCH(x,DatePIndex2,0),4))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Office2,MATCH(x,DatePIndex2,0),5)))), VSTACK(HSTACK(Office1[#Headers],Office2[#Headers]), IFERROR(HSTACK(Of1Result,Of2Result),"")) )
Dynamic array formulas.
 
Upvote 0
If you don't want to use tables:

TablasConEspacios - wo tables.xlsx
BCDEFGHIJKLM
2Office 1Office 2
3DatePO#JO#FROM-TOPRICEDatePO#JO#FROM-TOPRICE
430/10/202323554510001HMD-TOI3423430/10/202323445610056SDK-OSD5434
504/11/202323445610023FSK-IFM2342304/11/202323454310006OSD-SDK6645
608/11/202323454310033KFM-FKS23205/11/202323432510045DAN-CEW4564
708/11/202323432510034DIC-KKO245608/11/202323552410024.6667SDK-OSD4677.66667
810/11/202323552410056SDK-OSD654510/11/2023235458.510019.1667OSD-SDK4242.66667
910/11/202334632410006OSD-SDK45610/11/2023235757.110013.6667SDK-OSD3807.66667
1011/11/202335678410045DAN-CEW543411/11/2023236055.710008.1667OSD-SDK3372.66667
1115/11/202337894010046MNL-KLW664516/11/2023236354.310002.6667FSK-IFM2937.66667
1217/11/202339846710076PHP-USD456417/11/2023236652.910024.6667KFM-FKS2502.66667
1320/11/202325554510044JDL-JKD788920/11/2023236951.510019.1667PHP-USD2067.66667
14
15
16DatePO#JO#FROM-TOPRICEDatePO#JO#FROM-TOPRICE
1730/10/202323554510001HMD-TOI3423430/10/202323445610056SDK-OSD5434
1804/11/202323445610023FSK-IFM2342304/11/202323454310006OSD-SDK6645
1905/11/202323432510045DAN-CEW4564
2008/11/202323454310033KFM-FKS23208/11/202323552410024.6667SDK-OSD4677.66667
2108/11/202323432510034DIC-KKO2456
2210/11/202323552410056SDK-OSD654510/11/2023235458.510019.1667OSD-SDK4242.66667
2310/11/202334632410006OSD-SDK45610/11/2023235757.110013.6667SDK-OSD3807.66667
2411/11/202335678410045DAN-CEW543411/11/2023236055.710008.1667OSD-SDK3372.66667
2515/11/202337894010046MNL-KLW6645
2616/11/2023236354.310002.6667FSK-IFM2937.66667
2717/11/202339846710076PHP-USD456417/11/2023236652.910024.6667KFM-FKS2502.66667
2820/11/202325554510044JDL-JKD788920/11/2023236951.510019.1667PHP-USD2067.66667
Hoja1
Cell Formulas
RangeFormula
B16:K28B16=LET( O1Dates,Hoja1!$B$4:$B$13, O1DatesUnicos,HSTACK(UNIQUE(O1Dates),BYROW(UNIQUE(O1Dates), LAMBDA(x, SUM((x=O1Dates)*1)))), Frequency1, CHOOSECOLS(O1DatesUnicos,2), Index1,REDUCE(,Frequency1,LAMBDA(x,y,VSTACK(x,SEQUENCE(y)))), DatePIndex1, O1Dates+Index1/10000, O2Dates,Hoja1!$I$4:$I$13, O2DatesUnicos,HSTACK(UNIQUE(O2Dates),BYROW(UNIQUE(O2Dates), LAMBDA(x, SUM((x=O2Dates)*1)))), Frequency2, CHOOSECOLS(O2DatesUnicos,2), Index2,REDUCE(,Frequency2,LAMBDA(x,y,VSTACK(x,SEQUENCE(y)))), DatePIndex2,O2Dates+Index2/10000, AllDatesIndex,SORT(UNIQUE(VSTACK(DatePIndex1,DatePIndex2))), Of1WIndex,HSTACK(DatePIndex1,Hoja1!$B$4:$F$13), Of2WIndex,HSTACK(DatePIndex2,Hoja1!$I$4:$M$13), Of1Result, HSTACK(MAP(AllDatesIndex,LAMBDA(x,INDEX(Hoja1!$B$4:$F$13,MATCH(x,DatePIndex1,0),1))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Hoja1!$B$4:$F$13,MATCH(x,DatePIndex1,0),2))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Hoja1!$B$4:$F$13,MATCH(x,DatePIndex1,0),3))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Hoja1!$B$4:$F$13,MATCH(x,DatePIndex1,0),4))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Hoja1!$B$4:$F$13,MATCH(x,DatePIndex1,0),5)))), Of2Result, HSTACK(MAP(AllDatesIndex,LAMBDA(x,INDEX(Hoja1!$I$4:$M$13,MATCH(x,DatePIndex2,0),1))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Hoja1!$I$4:$M$13,MATCH(x,DatePIndex2,0),2))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Hoja1!$I$4:$M$13,MATCH(x,DatePIndex2,0),3))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Hoja1!$I$4:$M$13,MATCH(x,DatePIndex2,0),4))), MAP(AllDatesIndex,LAMBDA(x,INDEX(Hoja1!$I$4:$M$13,MATCH(x,DatePIndex2,0),5)))), VSTACK(HSTACK(Hoja1!$B$3:$F$3,Hoja1!$I$3:$M$3), IFERROR(HSTACK(Of1Result,Of2Result),"")) )
Dynamic array formulas.
 
Upvote 0
Thank you for taking interest in my problem.

I'm having troubles with the code.
It wants me to change Office1[Date], Office2[Date], Office1[#Headers] and Office2[#Headers].
What cell ranges do I select?


=LET(O1Dates,Office1[Date],O1DatesUnicos,HSTACK(UNIQUE(O1Dates),BYROW(UNIQUE(O1Dates), LAMBDA(x, SUM((x=O1Dates)*1)))),Frequency1, CHOOSECOLS(O1DatesUnicos,2),Index1,REDUCE(,Frequency1,LAMBDA(x,y,VSTACK(x,SEQUENCE(y)))),DatePIndex1, O1Dates+Index1/10000,O2Dates,Office2[Date],O2DatesUnicos,HSTACK(UNIQUE(O2Dates),BYROW(UNIQUE(O2Dates), LAMBDA(x, SUM((x=O2Dates)*1)))),Frequency2, CHOOSECOLS(O2DatesUnicos,2),Index2,REDUCE(,Frequency2,LAMBDA(x,y,VSTACK(x,SEQUENCE(y)))),DatePIndex2,O2Dates+Index2/10000,AllDatesIndex,SORT(UNIQUE(VSTACK(DatePIndex1,DatePIndex2))),Of1WIndex,HSTACK(DatePIndex1,Office1),Of2WIndex,HSTACK(DatePIndex2,Office2),Of1Result, HSTACK(MAP(AllDatesIndex,LAMBDA(x,INDEX(Office1,MATCH(x,DatePIndex1,0),1))),MAP(AllDatesIndex,LAMBDA(x,INDEX(Office1,MATCH(x,DatePIndex1,0),2))),MAP(AllDatesIndex,LAMBDA(x,INDEX(Office1,MATCH(x,DatePIndex1,0),3))),MAP(AllDatesIndex,LAMBDA(x,INDEX(Office1,MATCH(x,DatePIndex1,0),4))),MAP(AllDatesIndex,LAMBDA(x,INDEX(Office1,MATCH(x,DatePIndex1,0),5)))),Of2Result, HSTACK(MAP(AllDatesIndex,LAMBDA(x,INDEX(Office2,MATCH(x,DatePIndex2,0),1))),MAP(AllDatesIndex,LAMBDA(x,INDEX(Office2,MATCH(x,DatePIndex2,0),2))),MAP(AllDatesIndex,LAMBDA(x,INDEX(Office2,MATCH(x,DatePIndex2,0),3))),MAP(AllDatesIndex,LAMBDA(x,INDEX(Office2,MATCH(x,DatePIndex2,0),4))),MAP(AllDatesIndex,LAMBDA(x,INDEX(Office2,MATCH(x,DatePIndex2,0),5)))),VSTACK(HSTACK(Office1[#Headers],Office2[#Headers]),IFERROR(HSTACK(Of1Result,Of2Result),"")))
 
Upvote 0
In case a VBA solution is acceptable - and you want the changes to happen in situ - please try the following on a copy of your workbook:
VBA Code: 
Option Explicit
Sub bobbie()
    Application.ScreenUpdating = False
    Dim ws As Worksheet, i As Long
    Set ws = Worksheets("Sheet1")  '<-- *** change to actual sheet name ***
    With ws
        .Range("A2:E" & ws.Cells(Rows.Count, "E").End(xlUp).Row) _
        .Sort Key1:=ws.Range("A2"), order1:=xlAscending, Header:=xlYes
        .Range("G2:K" & ws.Cells(Rows.Count, "K").End(xlUp).Row) _
        .Sort Key1:=ws.Range("G2"), order1:=xlAscending, Header:=xlYes
        For i = 3 To .Cells(Rows.Count, "A").End(xlUp).Row * 2
            If .Cells(i, 1) <> "" And .Cells(i, 7) <> "" And .Cells(i, 1) < .Cells(i, 7) Then
                .Cells(i, 7).Resize(1, 5).Insert shift:=xlDown
            ElseIf .Cells(i, 1) <> "" And .Cells(i, 7) <> "" And .Cells(i, 1) > .Cells(i, 7) Then
                .Cells(i, 1).Resize(1, 5).Insert shift:=xlDown
            End If
        Next i
    End With
    Application.ScreenUpdating = True
End Sub

Before:
bobbie.xlsm
ABCDEFGHIJK
1Office 1Office 2
2DatePO#JO#FROM-TOPRICEDatePO#JO#FROM-TOPRICE
310/30/2323554510001HMD-TOI34,23410/30/2323445610056SDK-OSD5,434
411/04/2323445610023FSK-IFM23,42311/04/2323454310006OSD-SDK6,645
511/08/2323454310033KFM-FKS23211/05/2323432510045DAN-CEW4,564
611/08/2323432510034DIC-KKO2,45611/08/2323552410025SDK-OSD4,678
711/10/2323552410056SDK-OSD6,54511/10/2323545910019OSD-SDK4,243
811/10/2334632410006OSD-SDK45611/10/2323575710014SDK-OSD3,808
911/11/2335678410045DAN-CEW5,43411/11/2323605610008OSD-SDK3,373
1011/15/2337894010046MNL-KLW6,64511/16/2323635410003FSK-IFM2,938
1111/17/2339846710076PHP-USD4,56411/17/2323665310025KFM-FKS2,503
1211/20/2325554510044JDL-JKD7,88911/20/2323695210019PHP-USD2,068
13
Sheet1


After:
bobbie.xlsm
ABCDEFGHIJK
1Office 1Office 2
2DatePO#JO#FROM-TOPRICEDatePO#JO#FROM-TOPRICE
310/30/2323554510001HMD-TOI34,23410/30/2323445610056SDK-OSD5,434
411/04/2323445610023FSK-IFM23,42311/04/2323454310006OSD-SDK6,645
511/05/2323432510045DAN-CEW4,564
611/08/2323454310033KFM-FKS23211/08/2323552410025SDK-OSD4,678
711/08/2323432510034DIC-KKO2,456
811/10/2323552410056SDK-OSD6,54511/10/2323545910019OSD-SDK4,243
911/10/2334632410006OSD-SDK45611/10/2323575710014SDK-OSD3,808
1011/11/2335678410045DAN-CEW5,43411/11/2323605610008OSD-SDK3,373
1111/15/2337894010046MNL-KLW6,645
1211/16/2323635410003FSK-IFM2,938
1311/17/2339846710076PHP-USD4,56411/17/2323665310025KFM-FKS2,503
1411/20/2325554510044JDL-JKD7,88911/20/2323695210019PHP-USD2,068
15
Sheet1
 
Upvote 0
bobbieatendido said:
I'm having troubles with the code.
It wants me to change Office1[Date], Office2[Date], Office1[#Headers] and Office2[#Headers].
What cell ranges do I select?
Click to expand...

Office1[Date] is B4:B13
Office2[Date] is I4:I13
Office1[#Headers] is B3:F3
Office2[#Headers] is I3:M3

In post #3 I posted the formula without tables.
 
Upvote 0
Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Sort 2 data's by date but skipping rows depending on the other data's date
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.
 
Upvote 0
You must log in or register to reply here.

Similar threads

SFCChase
  • Question Question
Use Dropdown to search other tabs
Replies
4
Views
139
SFCChase
SFCChase
D
  • Question Question
Formula help needed for working with days and times.
Replies
0
Views
319
dshtesy
D
R
  • Solved
Age Group Ranging by gender
Replies
2
Views
259
Raj1979
R
S
  • Solved
Count of active employees between dates.
Replies
3
Views
667
Fluff
Fluff
M
  • Question Question
SLA Time Calculation
Replies
5
Views
342
felixstraube
F

Forum statistics

Threads
1,223,903
Messages
6,175,284
Members
452,630
Latest member
OdubiYouth

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top