Need Create Multiple Rows from a Merged Worksheet

MThies

New Member
Joined
Sep 1, 2021
Messages
14
Office Version
  1. 365
Platform
  1. Windows
I merged two worksheets together but I don't want the info merged into one row. I'd like one row to show the sales schedule and the row below to show the delivery schedule for each employee, based on the customer key. Could you help me find a way to ajust the info for all the data? Below are some examples of my data and how I would like it to look.
Thank you!

After Merge:
Book8 (version 1).xlsb
ABCDEFGH
1CusKeyCusNameService DaySales PersonCusKeyCusNameService DayDelivery Driver
2635101MJon635101TBill
3504302WJon504302THBill
4635083MJon635083TBill
5635464WJon635464THBill
Sheet3


How I would like it to look:
Book8 (version 1).xlsb
ABCDE
1CusKeyCusNameService DaySales PersonDelivery Driver
2635101MJon
3635101TBill
4504302WJon
5504302THBill
6635083MJon
7635083TBill
8635464WJon
9635464THBill
Sheet3
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
MrExcelPlayground6.xlsx
ABCDEFGH
1CusKeyCusNameService DaySales PersonCusKeyCusNameService DayDelivery Driver
2635101MJon635101TBill
3504302WJon504302THBill
4635083MJon635083TBill
5635464WJon635464THBill
6
7
8
9
10CusKeyCusNameService DaySales PersonDelivery Driver
11635101MJon 
12635101TBill
13504302WJon
14504302THBill
15635083MJon
16635083TBill
17635464WJon
18635464THBill
Sheet9
Cell Formulas
RangeFormula
A11:B18A11=INDEX(A2:A5,INT(SEQUENCE(2*ROWS(A2:A5),1,2,1)/2))
C11:C18C11=IF(ISEVEN(SEQUENCE(2*ROWS(C2:C5),1,2,1)),INDEX(C2:C5,INT(SEQUENCE(2*ROWS(C2:C5),1,2,1)/2)),INDEX(G2:G5,INT(SEQUENCE(2*ROWS(G2:G5),1,2,1)/2)))
D11:D18D11=IF(ISEVEN(SEQUENCE(2*ROWS(D2:D5),1,2,1)),INDEX(D2:D5,INT(SEQUENCE(2*ROWS(D2:D5),1,2,1)/2)),"")
E11:E18E11=IF(ISEVEN(SEQUENCE(2*ROWS(H2:H5),1,2,1)),"",INDEX(H2:H5,INT(SEQUENCE(2*ROWS(H2:H5),1,2,1)/2)))
Dynamic array formulas.
 
Upvote 0
With two Excel Tables (in this example Table6 and Table7) in power query:

Power Query:
let
    tblSales = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table6"]}[Content],"Index"),
    tblDelivery = Table.AddIndexColumn(Excel.CurrentWorkbook(){[Name="Table7"]}[Content],"Index"),
    Joined = tblSales & tblDelivery,
    Sorted = Table.Sort(Joined,{{"Index", Order.Ascending}}),
    Result = Table.RemoveColumns(Sorted,{"Index"})
in
    Result

Book2
ABCDEFGHIJKLMNOP
1CusKeyCusNameService DaySales PersonCusKeyCusNameService DayDelivery DriverCusKeyCusNameService DaySales PersonDelivery Driver
2635101MJon635101TBill635101MJon
3504302WJon504302THBill635101TBill
4635083MJon635083TBill504302WJon
5635464WJon635464THBill504302THBill
6635083MJon
7635083TBill
8635464WJon
9635464THBill
10
Sheet3
 
Upvote 0
Solution

Forum statistics

Threads
1,214,879
Messages
6,122,065
Members
449,064
Latest member
scottdog129

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