Jul 02 2019 05:55 AM
Hi All,
I have been searching like crazy but struggling to find a solution that specifically fits what I am aiming to achieve.
First week of every month, I hope to walk into a patch-cycle meeting with an excel spreadsheet containing a list of servers. Next to that list of servers is another column with the 'week.day' so when we get the server owner to i.e. agree for patching on week 1 day 3 that column would have 1.3 populated in there.
Because of the nature that we perform our updates, I need another sheet to have headers for each of the 'Week' values (which can be pre-populated) and populate the cells below with each server that is marked for that week.
I have tried using tools such as vlookup and some methods using =index but struggling to make any progress. I confess to being very new to complex Excel formulas so wondering if anyone has suggestions they could share?
I cannot deviate too greatly from the format above as it forms the basis of a macro-export to text file that I have which creates a text file per-week (based upon the header value) each of which contain the servers for that week/day. Any help would be greatly appreciated!
Jul 02 2019 10:40 PM
SolutionHi,
you can do this with the following macro, see attachment.
Sub TransferData()
Dim lngRow As Long
Dim lngRowmax As Long
Dim rngFind As Range
Dim lngRowMaxT As Long
Sheet2.Range("A2:X" & Sheet2.Rows.Count).Clear
With Sheet1
lngRowmax = .Range("a" & .Rows.Count).End(xlUp).Row
For lngRow = 1 To lngRowmax
Set rngFind = Sheet2.Rows(1).Find(what:=.Range("A" & lngRow).Value, lookat:=xlWhole)
If Not rngFind Is Nothing Then
lngRowMaxT = Sheet2.Cells(Sheet2.Rows.Count, rngFind.Column).End(xlUp).Row + 1
Sheet2.Cells(lngRowMaxT, rngFind.Column).Value = .Range("B" & lngRow).Value
End If
Next lngRow
End With
End Sub
Best regards
Bernd
Jul 03 2019 02:49 AM
Variant with formulas for such sample
In I2
=IFERROR(INDEX(Table1[Week],AGGREGATE(15,6,1/(COUNTIF($H$2:H$2,Table1[Week])=0)*(ROW(Table1[Week])-ROW(Table1[[#Headers],[Week]])), 1 )),"")
and drag to the right till empty cells appear.
In I3
=IFERROR(INDEX(Table1[Server],AGGREGATE(15,6,1/(Table1[Week]=I$2)*(ROW(Table1[Week])-ROW(Table1[[#Headers],[Week]])),(ROW()-ROW(I$2)))),"")
and drag to the right and down.
If you have version of Excel with coming dynamic arrays, when
in E2
=TRANSPOSE(UNIQUE(Table1[Week]))
in E3
=FILTER(Table1[Server],Table1[Week]=E$2)
and drag to the right
Jul 03 2019 08:07 AM
Exactly what I was looking for!
Thank you very much for this Bernd, that makes this process a great deal easier, now to apply your solution to my spreadsheet.
Jul 03 2019 08:12 AM
Jul 02 2019 10:40 PM
SolutionHi,
you can do this with the following macro, see attachment.
Sub TransferData()
Dim lngRow As Long
Dim lngRowmax As Long
Dim rngFind As Range
Dim lngRowMaxT As Long
Sheet2.Range("A2:X" & Sheet2.Rows.Count).Clear
With Sheet1
lngRowmax = .Range("a" & .Rows.Count).End(xlUp).Row
For lngRow = 1 To lngRowmax
Set rngFind = Sheet2.Rows(1).Find(what:=.Range("A" & lngRow).Value, lookat:=xlWhole)
If Not rngFind Is Nothing Then
lngRowMaxT = Sheet2.Cells(Sheet2.Rows.Count, rngFind.Column).End(xlUp).Row + 1
Sheet2.Cells(lngRowMaxT, rngFind.Column).Value = .Range("B" & lngRow).Value
End If
Next lngRow
End With
End Sub
Best regards
Bernd