Macro or Formula to Insert rows and copy data based on cell

May 18, 2017 at 14:24:46
Specs: Windows 7
Hi, I hope someone can help me. I have an Excel sheet, where there is a variety of data. In order to better analyze some raw data, I need Excel to detect "Arrival Date" and "Length of Stay", to determine how many times it needs to replicate the original line, and add a cell specifying the date which corresponds to each new line.

See More: Macro or Formula to Insert rows and copy data based on cell

Reply ↓  Report •


#1
May 20, 2017 at 18:32:56
What do you mean by "detect"?

Are you looking for a macro to run against an existing set of data and insert the rows or is this something that you want to happen automatically when a user enters data?

Please be more specific about your requirements and how your data is laid out, such as what columns need to be searched, etc. Keep in mind that we can't see your data from where we are sitting, so you need to tell us everything we need to know so that we can help you.

Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Reply ↓  Report •

#2
May 25, 2017 at 20:11:27
Hello, thank you for your response! Let me see if I can be a bit more specific. The data is a set number of columns (19 columns), but the lines can be variable (depending on the number of entries for the period). Each line represents information about a hotel reservation. We would like to have a method of multiplying the lines depending on the number of nights each reservation is made out for; also, each new line should represent the date of stay, as well as day of week. For instance: a reservation is marked as arriving on May 25th for 3 nights. In our current file, this is represented in a single lines. What we'd like to see if a way of (not manually!!!) converting that 1 line (representing the one reservation) into 3 lines (representing each of the nights of the guest's stay), and those 3 lines would have to have the date (May 25th, May 26th and May 27th) along with the day of the week corresponding to the date.
From the 19 columns, we would like to preserve all of the information, but we can actually eliminate 7 if needs be. Thank you once again!

Reply ↓  Report •

#3
May 26, 2017 at 03:23:29
What column contains the date?

What column contains the number of nights?

Does the the day of the week column already exist?
- If yes, what column contains the day?
- If no, what column do you want to contain the day?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

Related Solutions

#4
May 26, 2017 at 07:04:34
Good morning, thank you very much for your follow up. There are the headers of each column, as the regular report goes:

Hotel SELL_CHAN PCR/NON LEAD_NT Rm Nt Arrival LOS TA_NAME Date Booked Booked Category Stay Category RM_TYP REV Segment CONF Number Total Rms Bbk ADR Days in Advance of booking LOS

Out of these, as needed, I can get rid of Hotel, PCR/NON, TA_NAM, Date Booked, Booked Category, CONF Number, Days in Advance of Booking, and LOS. The "critical" columns are Arrival and LOS (length of stay). Please let me know if you need additional details. Thank you, once again!


Reply ↓  Report •

#5
May 26, 2017 at 08:59:21
I'll ask again:

What column contains the date?

What column contains the number of nights?

Does the the day of the week column already exist?
- If yes, what column contains the day?
- If no, what column do you want to contain the day?

Keep in mind that I cannot see your workbook from where I am sitting. If I am going to write any VBA code, I need to know exactly where the data that you are trying to "detect" resides. Providing a long list of column headings doesn't help because I don't know if your data starts in Column A or Column Q or Column ZZ. I don't know if there are merged columns, blank columns, etc.

You need to provide Column letters so that I can direct the code to search those columns.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#6
May 26, 2017 at 10:39:10
Sorry, did not see your questions. Allow me to address them.

Column "Arrival" (6th column) is the date when the guest arrives.
Column "LOS" (7th column) is the length of stay, or number of nights the client will be a guest.

Data starts on column A (Hotel) and ends on column S (LOS).
There are no merged columns.
The last 2 columns have no data.

I can't include a screenshot here, but let me try to give you data on a vertical way, and you could adapt it to a horizontal view (which is how my spreadsheet is configured):

HOTEL YRTSW
SELL_CHANNEL CRO
PCR/NON PCR
LEAD_NT 9
Rm Nt 1
Arrival 27-05-17
LOS 2
TA_NAME Expedia
Date Booked 18-05-17
Booked Category IRGFR
Stay Category IRGFR
RM_TYP JUTG
REV 1316
Segment Direct
CONF Number 76546233
Total Tms Bbk 2
ADR 658
Days in Advance
LOS

As mentioned, there are several columns I can remove, prior to having the macro work, if this makes it easier. I hope this is clear, but please ask me any additional details you may need. Thank you very much!


Reply ↓  Report •

#7
May 26, 2017 at 11:16:12
I also asked:

Does the the day of the week column already exist?
- If yes, what column contains the day?
- If no, what column do you want to contain the day?

Since the answer appears to be "no" please answer the appropriate question.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#8
May 26, 2017 at 12:24:35
Thank you! There is currently no Day Of Week column, which would need to be added anywhere (or replace one of the expendable columns mentioned before).

Reply ↓  Report •

#9
May 27, 2017 at 04:14:24

Reply ↓  Report •

Ask Question