Need help figuring out how to move/combine data over.

R

rduffieldc

New Member
Joined
Apr 30, 2024
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have a list of appointments in a spreadsheet. Some columns contain the same location of the appointment (ie 'hospital 1' is the header for column B & C). I'm trying to #1, move all of the appointments into one column, and #2, create/insert a new column for the associated hospital name (column header). Here is an example of what the sheet looks like:

Hospital 1Hospital 1Hospital 2Hospital 2Hospital 3Hospital 3
Person1Monday
Person2Tuesday
Person3Wednesday
Person4Tuesday
Person5Friday
Person6Monday
Person7Tuesday
Person8Wednesday
Person9Thursday
Person10Monday
Person11Monday
Person12Tuesday

And here is what I would like to end up with:


HospitalAppointment
Person1Hospital 1Monday
Person2Hospital 1Tuesday
Person3Hospital 1Wednesday
Person4Hospital 1Tuesday
Person5Hospital 3Friday
Person6Hospital 3Monday
Person7Hospital 3Tuesday
Person8Hospital 2Wednesday
Person9Hospital 2Thursday
Person10Hospital 2Monday
Person11Hospital 1Monday
Person12Hospital 1Tuesday

Any help you could provide would be much appreciated!!!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Try:
Book1
ABCDEFG
1Hospital 1Hospital 1Hospital 2Hospital 2Hospital 3Hospital 3
2Person1Monday
3Person2Tuesday
4Person3Wednesday
5Person4Tuesday
6Person5Friday
7Person6Monday
8Person7Tuesday
9Person8Wednesday
10Person9Thursday
11Person10Monday
12Person11Monday
13Person12Tuesday
14
15Person1Hospital 1Monday
16Person2Hospital 1Tuesday
17Person3Hospital 1Wednesday
18Person4Hospital 1Tuesday
19Person5Hospital 3Friday
20Person6Hospital 3Monday
21Person7Hospital 3Tuesday
22Person8Hospital 2Wednesday
23Person9Hospital 2Thursday
24Person10Hospital 2Monday
25Person11Hospital 1Monday
26Person12Hospital 1Tuesday
Sheet11
Cell Formulas
RangeFormula
A15:C26A15=LET(t,TEXTSPLIT(TEXTJOIN(",",,TOCOL(A2:A13&"|"&B1:G1&"|"&B2:G13)),"|",","),FILTER(t,CHOOSECOLS(t,3)<>""))
Dynamic array formulas.
 
Upvote 0
That absolutely worked for that example, but for some reason it isn't working on my actual sheet. Maybe I'm missing something, but here is some more actual data edited from my original doc:
Submission DateFirst NameLast NameHospital 1Hospital 1Hospital 1Hospital 1Hospital 2Hospital 2Hospital 2Hospital 3Hospital 3Hospital 3Hospital 4Hospital 4Hospital 4
Apr 30, 2024GPWednesday, Jun 19, 2024 2:30 PM-2:45 PM
Apr 30, 2024DWMonday, May 06, 2024 10:30 AM-10:45 AM
Apr 30, 2024CKTuesday, Jun 18, 2024 11:00 AM-11:15 AM
Apr 30, 2024JOThursday, May 30, 2024 3:00 PM-3:15 PM
Apr 30, 2024NGThursday, May 09, 2024 8:30 AM-8:45 AM
Apr 30, 2024RTThursday, May 09, 2024 9:30 AM-9:45 AM
Apr 30, 2024TATuesday, May 21, 2024 11:30 AM-11:45 AM
Apr 30, 2024ECThursday, May 09, 2024 6:30 AM-6:45 AM
Apr 30, 2024MDThursday, May 30, 2024 5:30 PM-5:45 PM
 
Upvote 0
Note this has a character limit of ~32,000.
Book1
ABCDE
1Submission DateFirst NameLast NameHospital 1Hospital 1
230-Apr-24GP
330-Apr-24DW
430-Apr-24CK
530-Apr-24JO
630-Apr-24NGThursday, May 09, 2024 8:30 AM-8:45 AM
730-Apr-24RTThursday, May 09, 2024 9:30 AM-9:45 AM
830-Apr-24TATuesday, May 21, 2024 11:30 AM-11:45 AM
930-Apr-24ECThursday, May 09, 2024 6:30 AM-6:45 AM
1030-Apr-24MD
11
12
1345412GPHospital 3Wednesday, Jun 19, 2024 2:30 PM-2:45 PM
1445412DWHospital 4Monday, May 06, 2024 10:30 AM-10:45 AM
1545412CKHospital 4Tuesday, Jun 18, 2024 11:00 AM-11:15 AM
1645412JOHospital 4Thursday, May 30, 2024 3:00 PM-3:15 PM
1745412NGHospital 1Thursday, May 09, 2024 8:30 AM-8:45 AM
1845412RTHospital 1Thursday, May 09, 2024 9:30 AM-9:45 AM
1945412TAHospital 1Tuesday, May 21, 2024 11:30 AM-11:45 AM
2045412ECHospital 1Thursday, May 09, 2024 6:30 AM-6:45 AM
2145412MDHospital 4Thursday, May 30, 2024 5:30 PM-5:45 PM
Sheet12
Cell Formulas
RangeFormula
A13:E21A13=LET(t,TEXTSPLIT(TEXTJOIN("*",,UNIQUE(TOCOL(A2:A10&"|"&B2:B10&"|"&C2:C10&"|" & D1:P1 & "|" & D2:P10))),"|","*"),FILTER(t,CHOOSECOLS(t,5)<>""))
Dynamic array formulas.
 
Upvote 0
Here is an alternative solution using Power Query

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Submission Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Submission Date", "First Name", "Last Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Book1
ABCDE
13Submission DateFirst NameLast NameAttributeValue
144/30/2024GPHospital 3Wednesday, Jun 19, 2024 2:30 PM-2:45 PM
154/30/2024DWHospital 49Monday, May 06, 2024 10:30 AM-10:45 AM
164/30/2024CKHospital 410Tuesday, Jun 18, 2024 11:00 AM-11:15 AM
174/30/2024JOHospital 410Thursday, May 30, 2024 3:00 PM-3:15 PM
184/30/2024NGHospital 12Thursday, May 09, 2024 8:30 AM-8:45 AM
194/30/2024RTHospital 12Thursday, May 09, 2024 9:30 AM-9:45 AM
204/30/2024TAHospital 12Tuesday, May 21, 2024 11:30 AM-11:45 AM
214/30/2024ECHospital 12Thursday, May 09, 2024 6:30 AM-6:45 AM
224/30/2024MDHospital 410Thursday, May 30, 2024 5:30 PM-5:45
Sheet1
 
Upvote 1
Solution
alansidman said:
Here is an alternative solution using Power Query

Power Query: 
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Submission Date", type date}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Submission Date", "First Name", "Last Name"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"

Book1
ABCDE
13Submission DateFirst NameLast NameAttributeValue
144/30/2024GPHospital 3Wednesday, Jun 19, 2024 2:30 PM-2:45 PM
154/30/2024DWHospital 49Monday, May 06, 2024 10:30 AM-10:45 AM
164/30/2024CKHospital 410Tuesday, Jun 18, 2024 11:00 AM-11:15 AM
174/30/2024JOHospital 410Thursday, May 30, 2024 3:00 PM-3:15 PM
184/30/2024NGHospital 12Thursday, May 09, 2024 8:30 AM-8:45 AM
194/30/2024RTHospital 12Thursday, May 09, 2024 9:30 AM-9:45 AM
204/30/2024TAHospital 12Tuesday, May 21, 2024 11:30 AM-11:45 AM
214/30/2024ECHospital 12Thursday, May 09, 2024 6:30 AM-6:45 AM
224/30/2024MDHospital 410Thursday, May 30, 2024 5:30 PM-5:45
Sheet1
Click to expand...
Oh my gosh this was the answer I was looking for!!!! so easy to do. Any way to stop the Attribute column from appending #s to what the data should be? Not a huge pain, but just wondering. Thanks so much for all your help!
 
Upvote 0
Didn't realize what was happening. You can split the column to extract only the beginning numbers. PQ appends the numbers because in your source file, you have the same header for multiple columns and that does not work in PQ.

To split the column, highlight it and click on Split Column. Then split the column on the number of characters. Delete the column that has the extra numbers.
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
Update Large Pivot Filter
Replies
5
Views
386
BenW71
B
T
  • Solved
Sending multiple emails using VBA
Replies
1
Views
282
Tej92
T
R
  • Solved
vlookup, INDEX, or SUMPRODUCT???
2
Replies
10
Views
419
KRice
K
tourless
  • Question
Sorting Rows into Columns
Replies
2
Views
353
tourless
tourless
N
  • Question
Extracting data from certains dates
Replies
5
Views
384
Fluff
Fluff

Forum statistics

Threads
1,216,095
Messages
6,128,795
Members
449,468
Latest member
AGreen17

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