Re: Assigning of Value after Validation
Help, anyone please?
Thanks,
Marvhie
Re: Assigning of Value after Validation
Help, anyone please?
Thanks,
Marvhie
Re: Removing spaces and still retain the Time format
Thank you Krishnakumar! It certainly did
It's an additional info
Hello everyone!
Looking for help - i have a series of date and time (one week at least - see attached sample file).
Column E is a result of concatenate of Columns B and C. However, in Column C - i have an entry that says "Off". Initially, i included an "If' statement that if C="Off" then it will just concatenate the value of "12:00:00 AM" instead of the word "Off". This works fine.
The challenge is with columns F and G. The value in column "F" is the converted value of column "E" to another time zone and it is required to have the format m/d/yy h:m AM/PM and column G is same value of E but requires a format of m/d/yy only. Adding "12:00:00 AM" is giving us an inaccurate date when converting to new time zone.
I am looking for a possible solution with the following:
1. instead of adding fixed value of "12:00:00 AM" in replacement of "Off", to copy any valid time (within the available time of same person), and
2. upon conversion, it would still show a consecutive dates.
The set of entries (names) may vary - 10 to 20.
Any help will be greatly appreciated.
Thank you in advance.
Marvhie
Re: Removing spaces and still retain the Time format
Hi Skywriter,
Sorry for the late reply, but for some reason it already worked
Thanks!
Marvhie
Re: Removing spaces and still retain the Time format
Hi Skywriter,
Your code worked perfectly for majority of the entries - that is those with spaces from the left. I still have the dilemma for those with spaces on the right. Any more tricks?
Still thank you for the initial solution
Thanks,
Marvhie
Hello,
I have a schedule data with format as "1:00 PM - 10:00 PM". When doing the text to column, with (-) as my delimiter, the left time (1:00 PM) is read as correct time format and appeared as 1:00:00 PM. However, for the right time (10:00 PM), it was not read as time but instead as text, showing in cell as plain " 10:00 PM ". I tried using TRIM, CLEAN, SUBSTITUTE (and combined) but still it is showing as text.
Any help is greatly appreciated, whether a simple formula or VBA
Thanks,
Marvhie
Re: Schedule Mapping
Hi Mikerickson,
Apologies - I got your code now. It was looking for specific header "NAME". I was able to run it without any error now. I thank you so much. It is a big help!
Thanks,
Marvhie
Re: Schedule Mapping
Hi Mikerickson,
I tried your code and it did not give me anything. The macro stopped at Ln39:
DestinationData(Pointer, 3) = Split(sourceData(i, j), "-")(0)
For the sourcerange, do I just select the header/s of the columns or the whole column?
For the destinationrange, is it possible to just create a new sheet, then paste the values starting from A1?
Thanks,
Marvhie
Re: Schedule Mapping
Hello,
Any help from anyone please?
Thanks,
Marvhie
Hello,
We have schedule entries from my colleagues for one week, every week, in a columnar view and I am rearranging it manually to tabular view for easy reference of other activities. Can I ask for help to do it in macro? I have attached sample schedule entries and our intended output. Appreciate any help
Thanks,
Marvhie
I have been looking for a code that will copy set of text values and paste it to another worksheet (existing sheet) multiple times. The number of times it will paste the copied values will depend on the value from another worksheet (specific cell).
Example
Sheet1 (from A2)
Curlysue
Cora
Emz
Mhean
Minda
Beng
Banana
Maria
Sheet2 (A2)
16
Sheet3 (from A2 down)
Curlysue
Cora
Emz
Mhean
Minda
Beng
Banana
Maria
Curlysue
Cora
Emz
Mhean
Minda
Beng
Banana
Maria
Curlysue
Cora
Emz
Mhean
Minda
Beng
Banana
Maria.... (paste 16 times)
Will appreciate any help... Thanks in advance
Marvhielnx
Re: Would want to automatically fill and repeat the data for a given number of days
Hi Salim,
Another one, when I tried to increase the number of times (ex from 5 to 50), the entry number is no longer showing after row 124. Not only that, names and scores did not show the complete list of names and scores after row 124. From row 125, it only showed Names 1 to 5 but without the entry number.
Thanks again,
Marvhielnx
Hello,
Would like to ask help on how to automatically fill columns in worksheet (B) wherein the raw data will be entered in worksheet (A) and the desired output is to have that data populated in worksheet (B) and will be repeated (all raw data) depending on the number that will be provided in one cell, let's say C1 in worksheet (A). For example, in worksheet (A), I have 5 names with corresponding scores (1 set) to be repeated 15 times. This set of data will be populated in worksheet (B) for 15 times with additional 3rd column to identify the number of set already.
Raw Data (worksheet A)
[TABLE="width: 192"]
[TD="class: xl65, width: 64, align: center"]Names[/TD]
[TD="class: xl65, width: 64, align: center"]Score[/TD]
[TD="width: 64, align: center"]15[/TD]
[TD="class: xl66, align: center"]Name1[/TD]
[TD="class: xl66, align: center"]85[/TD]
[TD="align: center"][/TD]
[TD="class: xl66, align: center"]Name2[/TD]
[TD="class: xl66, align: center"]86[/TD]
[TD="align: center"][/TD]
[TD="class: xl66, align: center"]Name3[/TD]
[TD="class: xl66, align: center"]79[/TD]
[TD="align: center"][/TD]
[TD="class: xl66, align: center"]Name4[/TD]
[TD="class: xl66, align: center"]65[/TD]
[TD="align: center"][/TD]
[TD="class: xl66, align: center"]Name5[/TD]
[TD="class: xl66, align: center"]88[/TD]
[/TABLE]
Desired Output (worksheet B)
[TABLE="width: 192"]
[TD="align: center"]Names[/TD]
[TD="align: center"]Score[/TD]
[TD="align: center"]Entry[/TD]
[TD="align: center"]Name1[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Name2[/TD]
[TD="align: center"]86[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Name3[/TD]
[TD="align: center"]79[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Name4[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Name5[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]Name1[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Name2[/TD]
[TD="align: center"]86[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Name3[/TD]
[TD="align: center"]79[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Name4[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]Name5[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="colspan: 3, align: center"]……………………………………………..[/TD]
[TD="colspan: 3, align: center"]……………………………………………..[/TD]
[TD="align: center"]Name1[/TD]
[TD="align: center"]85[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]Name2[/TD]
[TD="align: center"]86[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]Name3[/TD]
[TD="align: center"]79[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]Name4[/TD]
[TD="align: center"]65[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]Name5[/TD]
[TD="align: center"]88[/TD]
[TD="align: center"]15[/TD]
[/TABLE]
Re: Use an opened file in formula
Hi Luke,
Can we have them browse thru folders to choose the file? and is it ActiveCell.FormulaF1C1 not ActiveCell.FormulaR1C1?
Is it possible to insert the filename automatically in formula after the input from the user?
I want to ask the user to input a filename and afterwards, call that filename in an Index-Match
I have the below formula with the sample filename:
ActiveCell.FormulaR1C1 = _
"=INDEX('[filename1.xlsx]DATA'!C9,MATCH(RC[1],'[filename1.xlsx]DATA'!C32,0))"
Range("A2").SelectThanks in advance!
marvhie
Re: Possible solution to distribute/breakdown items into 15-min interval
Hi Dan,
Just a follow up question
Since the pivot table is dynamic - headers, start time, end time are changing - is there another way to adjust the formula so it will automatically recalculate? As I understand it, the formula you gave is specific to the sample table I gave. My actual data is bigger and has different dates (sample July 19, earliest time is 8:00 am and will end July 20, 2:00am). I was able to replicate the formula you have provided and it was successful. However, when I changed the data (there were changes in ,date, lesser headers, start and end time), I had to change the formula again. If this is a limitation in Excel is it possible to create a macro?
Thanks,
marvhielnx