Help with a complicated macro

B

Busscheduler

New Member
Joined
Nov 23, 2020
Messages
41
Office Version
  1. 2016
Platform
  1. Windows
Hi everyone, I am looking for help with a macro that will sort a list of guests that is dependent on what resort they are staying at and then open a new tab and place that information in a certain format. Here is the information that I am working with in Excel:

I apologize if I need to upload the spreadsheets. Please let me know and I will do that instead of jpegs.

Raw Data.png


The macro would need to sort each group by resort name, since the number of rows would change daily depending on how many guests they have that day, collect only the names for that resort and put them together in the format below.

Desired Result.png

I have limited skills when it comes to doing Macros, so any help would be greatly appreciated.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
Sort by date Sort by votes
I will do this from home tonight. I don't have the ability, at work, to open the zip file and add it to Excel. Thank you alansidman for your quick response.
 
Upvote 0
Ok, so here is the data that I am working with:

TUI TEST FILE.xls
RSTUV
610:50Coco Key Hotel and Water Park ResortH. Mcgowan - 87684 - Party of 4
710:50Coco Key Hotel and Water Park ResortA. Loveridge - 87909 - Party of 2
810:50Coco Key Hotel and Water Park ResortT. Gambiza - 88308 - Party of 2
910:55Rosen Inn InternationalA. Grayson - 87682 - Party of 5
1010:55Rosen Inn InternationalD. Thomson - 87904 - Party of 3
1110:55Rosen Inn InternationalS. ****er - 87683 - Party of 2
1211:05Sonesta ES Suites OrlandoMORRIS - 89407 - Party of 2
1311:15Rosen Inn Pointe OrlandoBUS 2 (34)C. Baines - 87688 - Party of 2
1411:15Rosen Inn Pointe OrlandoK. Eckersley - 87911 - Party of 4
1511:15Rosen Inn Pointe OrlandoK. Graham - 87659 - Party of 4
1611:20Ramada Plaza Resort and Suites International DriveM. HARRIS - 88431 - Party of 2
1711:20Ramada Plaza Resort and Suites International DriveS. newham - 88577 - Party of 2
Sheet0
Cell Formulas
RangeFormula
V6:V17V6=CONCATENATE(X6," - ",W6," - ","Party of ",Y6)


And here is what the desired outcome would be:

TUI TEST FILE.xls
BC
510:50Coco Key Hotel and Water Park Resort
6 H. Mcgowan - 87684 - Party of 4
7 A. Loveridge - 87909 - Party of 2
8 T. Gambiza - 88308 - Party of 2
910:55Rosen Inn International
10 A. Grayson - 87682 - Party of 5
11 D. Thomson - 87904 - Party of 3
12 S. ****er - 87683 - Party of 2
1311:05Sonesta ES Suites Orlando
14 MORRIS - 89407 - Party of 2
15
16etc., etc., etc.
17
Sheet1


Thanks for any help I can get on this one. It would save a lot of time and would cut down on the number of errors while retyping the information.
 
Upvote 0
Thanks for uploading an XL2BB file, however, you only loaded the visible data. Column V is a formula that is dependent on data that you did not include in the upload. I cannot do anything with this data without having to retype it all. Suggest you copy and paste data only and not formulas so that we can work with the data. One of the quirks of working with this type of situation.
 
Upvote 0
I don't have PQ but try this...
VBA Code: 
Sub MM1()
 Dim lr As Long, r As Long, x As Integer
 lr = Cells(Rows.Count, "R").End(xlUp).Row
     Cells(1, "S").Copy Cells(1, "B")
 For r = 2 To lr + 1
    If Cells(r, "S") <> Cells(r - 1, "S") Then
        Cells(r, "S").Copy Cells(r, "B")
    End If
 Cells(r - 1, "V").Copy Cells(r - 1, "C")
 Next r
End Sub
 
Upvote 0
Solution
alansidman said:
Thanks for uploading an XL2BB file, however, you only loaded the visible data. Column V is a formula that is dependent on data that you did not include in the upload. I cannot do anything with this data without having to retype it all. Suggest you copy and paste data only and not formulas so that we can work with the data. One of the quirks of working with this type of situation.
Click to expand...
I'll give it another try:

Copy of TUI - Departures - 4-11-22 (20220406).xls
RSTUVWXY
610:50Coco Key Hotel and Water Park ResortH. Mcgowan - 87684 - Party of 487684H. Mcgowan4
710:50Coco Key Hotel and Water Park ResortA. Loveridge - 87909 - Party of 287909A. Loveridge2
810:50Coco Key Hotel and Water Park ResortT. Gambiza - 88308 - Party of 288308T. Gambiza2
910:55Rosen Inn InternationalA. Grayson - 87682 - Party of 587682A. Grayson5
1010:55Rosen Inn InternationalD. Thomson - 87904 - Party of 387904D. Thomson3
1110:55Rosen Inn InternationalS. ****er - 87683 - Party of 287683S. ****er2
1211:05Sonesta ES Suites OrlandoMORRIS - 89407 - Party of 289407MORRIS2
1311:15Rosen Inn Pointe OrlandoBUS 2 (34)C. Baines - 87688 - Party of 287688C. Baines2
1411:15Rosen Inn Pointe OrlandoK. Eckersley - 87911 - Party of 487911K. Eckersley4
1511:15Rosen Inn Pointe OrlandoK. Graham - 87659 - Party of 487659K. Graham4
1611:20Ramada Plaza Resort and Suites International DriveM. HARRIS - 88431 - Party of 288431M. HARRIS2
1711:20Ramada Plaza Resort and Suites International DriveS. newham - 88577 - Party of 288577S. newham2
Sheet0
 
Upvote 0
Michael M said:
I don't have PQ but try this...
VBA Code: 
Sub MM1()
 Dim lr As Long, r As Long, x As Integer
 lr = Cells(Rows.Count, "R").End(xlUp).Row
     Cells(1, "S").Copy Cells(1, "B")
 For r = 2 To lr + 1
    If Cells(r, "S") <> Cells(r - 1, "S") Then
        Cells(r, "S").Copy Cells(r, "B")
    End If
 Cells(r - 1, "V").Copy Cells(r - 1, "C")
 Next r
End Sub
Click to expand...
Michael M:
I tried the code and nothing changed. I'm not sure if I have the correct range. I highlighted the information and then ran the macro. Can you tell me which part of the code defines the range or is it the entire sheet? I'm kind of a newbie to building macros.
 
Upvote 0
Busscheduler said:
Michael M:
I tried the code and nothing changed. I'm not sure if I have the correct range. I highlighted the information and then ran the macro. Can you tell me which part of the code defines the range or is it the entire sheet? I'm kind of a newbie to building macros.
Click to expand...
Michael M:
I did find the following changes to the spreadsheet.

This is the original cells:
TUI - Departures - 4-11-22 (20220406).xls
ABCD
1Airport TerminalTransfer PlanFlight Time From-ToDate From-To
2Melbourne Airport - Florida576310415:5511/04/2022
3Melbourne Airport - Florida576310415:5511/04/2022
4Melbourne Airport - Florida576310415:5511/04/2022
5Melbourne Airport - Florida576310415:5511/04/2022
6Melbourne Airport - Florida576310415:5511/04/2022
7Melbourne Airport - Florida576310415:5511/04/2022
8Melbourne Airport - Florida576310415:5511/04/2022
9Melbourne Airport - Florida576310415:5511/04/2022
10Melbourne Airport - Florida576310415:5511/04/2022
11Melbourne Airport - Florida576310415:5511/04/2022
12Melbourne Airport - Florida576310415:5511/04/2022
13Melbourne Airport - Florida576310415:5511/04/2022
14Melbourne Airport - Florida576310415:5511/04/2022
15Melbourne Airport - Florida576310415:5511/04/2022
16Melbourne Airport - Florida576310415:5511/04/2022
17Melbourne Airport - Florida576310415:5511/04/2022
18Melbourne Airport - Florida576310415:5511/04/2022
19Melbourne Airport - Florida576310415:5511/04/2022
20Melbourne Airport - Florida576310415:5511/04/2022
21Melbourne Airport - Florida576310415:5511/04/2022
22Melbourne Airport - Florida576310415:5511/04/2022
23Melbourne Airport - Florida576310415:5511/04/2022
24
25Melbourne Airport - Florida576310818:2511/04/2022
26Melbourne Airport - Florida576310818:2511/04/2022
27Melbourne Airport - Florida576310818:2511/04/2022
28Melbourne Airport - Florida576310818:2511/04/2022
29Melbourne Airport - Florida576310818:2511/04/2022
30Melbourne Airport - Florida576310818:2511/04/2022
31Melbourne Airport - Florida576310818:2511/04/2022
32Melbourne Airport - Florida576310818:2511/04/2022
33Melbourne Airport - Florida576310818:2511/04/2022
34Melbourne Airport - Florida576310818:2511/04/2022
35Melbourne Airport - Florida576310818:2511/04/2022
36Melbourne Airport - Florida576310818:2511/04/2022
37Melbourne Airport - Florida576310818:2511/04/2022
38Melbourne Airport - Florida576310818:2511/04/2022
39Melbourne Airport - Florida576310818:2511/04/2022
40Melbourne Airport - Florida576310818:2511/04/2022
Sheet0


And this is the result of running the code:

Copy of TUI - Departures - 4-11-22 (20220406).xls
ABCD
2Melbourne Airport - FloridaClarion Lake Buena VistaMEARS - 11/04/2022 - Party of BUS 5011/04/2022
3Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
4Melbourne Airport - FloridaDisney's All Star Music ResortMEARS - 11/04/2022 - Party of BUS 5011/04/2022
5Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
6Melbourne Airport - FloridaCoco Key Hotel and Water Park ResortMEARS - 11/04/2022 - Party of BUS 5011/04/2022
7Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
8Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
9Melbourne Airport - FloridaRosen Inn InternationalMEARS - 11/04/2022 - Party of BUS 5011/04/2022
10Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
11Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
12Melbourne Airport - FloridaSonesta ES Suites OrlandoMEARS - 11/04/2022 - Party of BUS 5011/04/2022
13Melbourne Airport - FloridaRosen Inn Pointe OrlandoMEARS - 11/04/2022 - Party of BUS 5011/04/2022
14Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
15Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
16Melbourne Airport - FloridaRamada Plaza Resort and Suites International DriveMEARS - 11/04/2022 - Party of BUS 1011/04/2022
17Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 1011/04/2022
18Melbourne Airport - FloridaThe Avanti ResortMEARS - 11/04/2022 - Party of BUS 5011/04/2022
19Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
20Melbourne Airport - FloridaUniversal's Endless Summer ResortMEARS - 11/04/2022 - Party of BUS 5011/04/2022
21Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
22Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
23Melbourne Airport - Florida5763104MEARS - 11/04/2022 - Party of BUS 5011/04/2022
24
25Melbourne Airport - FloridaDisney's Caribbean BeachMEARS - 11/04/2022 - Party of BUS 5011/04/2022
26Melbourne Airport - FloridaClarion Lake Buena VistaMEARS - 11/04/2022 - Party of BUS 5011/04/2022
27Melbourne Airport - FloridaDisney's Coronado Springs ResortMEARS - 11/04/2022 - Party of BUS 5011/04/2022
28Melbourne Airport - Florida5763108MEARS - 11/04/2022 - Party of BUS 5011/04/2022
29Melbourne Airport - FloridaRosen Inn InternationalMEARS - 11/04/2022 - Party of BUS 5011/04/2022
30Melbourne Airport - Florida5763108MEARS - 11/04/2022 - Party of BUS 5011/04/2022
31Melbourne Airport - FloridaSonesta ES Suites OrlandoMEARS - 11/04/2022 - Party of BUS 5011/04/2022
32Melbourne Airport - FloridaRosen Inn Pointe OrlandoMEARS - 11/04/2022 - Party of BUS 5011/04/2022
33Melbourne Airport - FloridaRamada Plaza Resort and Suites International DriveMEARS - 11/04/2022 - Party of BUS 5011/04/2022
34Melbourne Airport - Florida5763108MEARS - 11/04/2022 - Party of BUS 5011/04/2022
35Melbourne Airport - Florida5763108MEARS - 11/04/2022 - Party of BUS 5011/04/2022
36Melbourne Airport - FloridaLoews Royal Pacific Resort at Universal OrlandoMEARS - 11/04/2022 - Party of BUS 5011/04/2022
37Melbourne Airport - FloridaUniversal's Endless Summer ResortMEARS - 11/04/2022 - Party of BUS 5011/04/2022
38Melbourne Airport - Florida5763108MEARS - 11/04/2022 - Party of BUS 5011/04/2022
39Melbourne Airport - Florida5763108MEARS - 11/04/2022 - Party of BUS 5011/04/2022
40Melbourne Airport - FloridaRosen Shingle CreekMEARS - 11/04/2022 - Party of BUS 5011/04/2022
Sheet0


What I would like is a new tab opened in the macro that would list the captured information instead of mixing it in with the other cells. Would the entire worksheet be of help?
 
Upvote 0
The code I provided assumes the data is in columns "R" "S" and "V"
it pastes the required data to columns "B" and "C" as per your original sample post.
However, the data you posted in post #9 is completely different from the original, so obviously wouldn't give the desired results !!
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Macro to deduplicate based on A and delete?
2
Replies
12
Views
181
mumps
mumps
C
  • Solved
VLOOKUP in Checkbox Macro
Replies
7
Views
105
Chestertim
C
A
  • Question
New to the Forum - looking for VBA Help in sorting selected cells
Replies
7
Views
299
kevin9999
K
J
  • Question
Insert Row based on cell Value VBA
Replies
2
Views
73
mumps
mumps
F
  • Solved
How to format a vendor list when vendors offer several different products?
Replies
8
Views
219
Micron
M

Forum statistics

Threads
1,215,503
Messages
6,125,179
Members
449,212
Latest member
kenmaldonado

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