Re: Create/Update sheets based on added/modified information on master sheet
Hi wizcreatioins
If I had the time to develop this from scratch I would have developed the code so whether there is a sheet existing or a new one is being created that the code to assign awards gets called once, not twice as I have it. It is just more efficient that way. It works ok though and Jindon has supplied some coding that looks good. Good luck with your project.
Take care
Smallman
Re: Distribute values of cells to different worksheets based on name
Hi Errtu
I think this should do what you are looking for. I guess these caveats need to be in play. You need all the names in the list to have a corresponding sheet name. There are no error trapping for this not to occur. The sheet name must match the name in column 1 exactly. Additionally it assumes you are using col 1 only for item names.
Take care
Smallman
Option Explicit
Sub AddtoSheet()
Dim i As Integer
Dim shNm As String
Dim ws As Worksheet
Dim lw As Integer
Set ws = ActiveSheet
For i = 2 To Range("A65536").End(xlUp).Row
shNm = Cells(i, 1)
lw = Sheets(shNm).Range("A" & Rows.Count).End(xlUp).Row + 1
With ws
.Range(.Cells(i, 1), .Cells(i, 4)).Copy
Sheets(shNm).Cells(lw, 1).PasteSpecial xlPasteValues
Sheets(shNm).Cells(lw, 5).Value = Now
End With
Next i
End SubRe: taking cells from a list to the next column, using VBA
Hi Shawn
Do you have something other than names in the workbook as an identifier for the employee - a staff number perhaps. This format is problematic as everything is text and this Smith. John the "." instead of the comma which the other two names have is a problem. You could look for the comma provided none of the descriptive data has a comma. Alternatively you could look for cells in Col A which don't have a "-" in them. Obviously this is problematic for people who have a double barren surname. What I am getting at is you need some unique identifier surrounding the name. If the staff name can't be differentiated from the descriptive data then there are all sorts of issues.
Take care
Smallman
Re: Hide rows based on day of week
Hi Lucas
This should do what you want. Hides the dates if they are the weekend. Based on your file with the dates in Col 1. Let the group know if it is not what you are after.
Take care
Smallman
Option Explicit
Sub HideWkend()
Dim i As Integer
Application.ScreenUpdating = True
For i = Range("A65536").End(xlUp).Row To 1 Step -1
If Weekday(Cells(i, 1)) = vbSaturday Or Weekday(Cells(i, 1)) = vbSunday Then
Cells(i, 1).EntireRow.Hidden = True
End If
Next i
Application.ScreenUpdating = False
End SubRe: Create/Update sheets based on added/modified information on master sheet
Hi wizcreations
The attached will do what you are looking for. It is not how I would want from scratch as I have duplicated the award coding to get it working. However it goes OK. Let us know if you have further enhancements in mind or it is not performing as expected.
Take care
Smallman
Re: Trying to go carry data from one sheet to another and have the formula rows incr
Hi Julia
If it is not too much trouble can you put the spreadsheet online. When i said a snipped I ment a smaller version of your data in a Spreadsheet. It will help everyone if your spreadsheet is on here.
Take care
Smallman
Re: Calculate Number of people on shift each hour
Hi Daveyboy
Ah as I said at the start this one is all about how you set your spreadsheet up. Set up correctly, this is a doddle. As for the format there is no way to capture the format 9:30 - 14:15 and extract time related shift information. The reason is this 9:30 - 14:15 is text. Text is not a number so can't be calculated in the same way as if the data was set out in a logical numerical way. Perhaps if you post your spreadsheet we might be able to help.
Re: Calculate Number of people on shift each hour
Hi Daveboy
This one is all about how you set up your spreadsheet. In the example attached I have set it up so there is a start time and an end time for each person. On a second sheet (StaffCount) sheet I have the times and the following calculation.
=SUMPRODUCT((Times!$B$2:$B$6<=$A14)*(Times!$C$2:$C$6>=$A14))
Please look at the file to see how the data is set out and how it captures each employee. The numbers tie in with what you expected at the base of your post. Hope this helps.
Take care
Smallman
Re: Trying to go carry data from one sheet to another and have the formula rows incr
Hi Julia
Can you please post your spreadsheet? Or a snippet of it. It might help to see your problem in XL. Oh if you include an example sheet that includes what your outcome will look like that would be really helpful.
Take care
Smallman
Re: Append certain cells from multiple rows to create one row
Hi Ken
Attached is your most recent spreadsheet. I have noticed some items which I believe to be errors in your source data and highlighted these in Red. Please look at the two hightlighted cells on the Example sheet. Don't move through this issue without addressing it. There are comments attached.
I have altered the coding so Column 2 is the unique identifier rather than if they are an Associate or not. As for the headings why don't you just have the Drop sheet setup as a template which exists only to receive the newly formatted data. Also this is important, there needs to be information in the Full/Part column, last column. In the example sheet I have put data for everyone in this column. These cells are important as the code relies on it to find the last used column in the Drop sheet. If your data does not have this column populated then it will not perform as expected. This won't be a problem if this column if populated as it should be. Anyways hope this helps.
Take care
Smallman
Re: Append certain cells from multiple rows to create one row
Hi Ken
The spreadsheet attached PostAll Recombinations2.xls is not the results you originally asked for in your Family File post 1. In fact, the solution you asked for has been answered. I will have a look at your latest incarnation. I don't think it will be a problem to alter but please try to articulate exactly what you want right off the bat. :smile:
Oh is this the full listing of people or are there more? There certainly will be issues if you don't have your data structured in the same way as the example file. If Associate is not the parent in each case this gets a bit more involved. Do you have more complete data with an example where your example for the Associate does not hold?
Take care
Smallman
Re: Append certain cells from multiple rows to create one row
Hi Ken
The file attached consolidates all the Associate data which you specified into one line. As I said earlier I can only imagine you would want to put it in one line to load into a separate system. The results of the attached file don't exactly match your results and I think this is because you pasted your data with formula. Pasting the values removes this potential issue. For those who are not members here is the coding. The results of the procedure are on the Drop sheet. You will have to clear this sheet if you want to run it from scratch or be aware that the new data will append to the bottom of the Drop sheet.
Take care
Smallman
Option Explicit
Sub CreateNew()
Dim i As Long
Dim j As Integer
Dim lw As Long
Dim lr As Long
Dim ws As Worksheet
Dim sh As Worksheet
Application.ScreenUpdating = False
Set ws = Sheets("Example")
Set sh = Sheets("Drop")
lw = ws.Range("A65536").End(xlUp).Row
j = 1
For i = 2 To lw
If ws.Range("F" & i).Value = "Associate" Then
j = j + 1
lw = sh.Range("A65536").End(xlUp).Row + 1
With ws
.Range(.Cells(i, 1), .Cells(i, 10)).Copy
sh.Range("A" & lw).PasteSpecial xlPasteValues
End With
Else
lr = sh.Range("A65536").End(xlUp).Row
j = j + 1
With ws
.Range(.Cells(j, 1), .Cells(j, 6)).Copy
sh.Range("IV" & lr).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteValues
.Cells(j, 18).Copy
sh.Range("IV" & lr).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteValues
End With
End If
Next
Application.ScreenUpdating = True
End SubRe: Append certain cells from multiple rows to create one row
The data in the file provided has a result line which is what Ken is looking for. The question I have is why you would want to take a perfectly tabular table, superb in every way for analysis and make a string of like fields. Do you want to load it into a seperate system? What you are asking for is not difficult to achieve. I just don't see the benefit over the format the data is currently in.
Take care
Smallman
Re: Duplicate value function
Hi Boo
Interesting problem. Part of the solutioin requires the use of array formula but I would be interested to see if someone can come up with a solution without it. So in the attached file you need to hit ctrl shift enter to get the formula to work.
For those who are guests here is the formula
1. To work out the unique schools (Normal formula not an array).
Total Number of Schools
=SUMPRODUCT((A2:A10<>"")/COUNTIF(A2:A10,A2:A10)
The above will have trouble if there are any blank rows where the school is supposed to be, but works well otherwise.
Total Number of Schools Passed (Array Formula, ctrl shift Enter)
=COUNT(1/FREQUENCY(IF($C$2:$C$11="Pass",IF($C$2:$C$11<>"",IF($A$2:$A$11<>"",MATCH($A$2:$A$11,$A$2:$A$11,0)))),ROW($A$2:$A$11)-ROW($A$2)+1))
The attached file should help.
Take care
Smallman
Re: Append to Excel 'Template' data from other Excel workshee
Hi Robin
I am sorry you only got the code to work once. The file which is attached is recursive, it runs over and over again, without fail. So in a perfect world the theory holds, so what is different when you add your data? I would go to lengths to ensure that the headings are identical between your two sheets. Secondly look at the number of headings to ensure the right amount of loops are performed. Ensure you extend the search Range in row 1 so it covers more of the first Row than you need. In essence ensure your data is as tight as the test environment set up in the attached spreadsheet.
Take care
Smallman
Re: Append to Excel 'Template' data from other Excel workshee
Hi Robin
I hope I have understood you correctly. You want to find the location of headings in the data tab and return the data in to the maching heading in the Templates sheet. The attached file should do what you are asking for. Here is the code for those who can not open the file. The file has two sheets. A 'Template' sheet and a 'Data' sheet. The code looks for the headers along the first row in the 'Template' sheet adds them to a variable then finds the position of the column in the 'Data' sheet. It then knows to copy this column to the 'Template' sheet (1000 rows assumed). It assums your data is ordered in the 'Template' sheet. It also assumes that you have exact match headings as you said this in your post. Error trapping for non matching headers has not been provided. Oh and the loop only covers 20 headings for demonstation purposes. Change to 40 or however many headings you are looking up. I hope this helps.
It is geting late here in Oz so if you do reply this evening I will do my best to get back to you in the morning. However there are many wonderful altruistic people not being hit by the sandman at this time who I am sure will be happy to help.
Take Care
Smallman
Option Explicit
Sub FindandCopy()
Dim fd As Integer
Dim j As Integer
Dim i As Integer
Dim sh As Worksheet
Dim ws As Worksheet
Dim sVar As String
Set sh = Sheets("Template")
Set ws = Sheets("Data")
'Set your Columns to 40 to cope with fill data set. Ensure headers match.
For i = 1 To 20
sVar = sh.Cells(1, i)
fd = ws.Range("A1:Z1").Find(sVar, ws.Range("A1"), xlValues, xlWhole).Column
With ws
Range(.Cells(2, fd), .Cells(1000, fd)).Copy sh.Cells(2, i) 'Change 1000 to whatever you like
End With
Next i
End Sub