07-02-2019 05:55 AM
07-02-2019 05:55 AM
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!
07-02-2019 10:40 PMSolution
you can do this with the following macro, see attachment.
Dim lngRow As Long
Dim lngRowmax As Long
Dim rngFind As Range
Dim lngRowMaxT As Long
Sheet2.Range("A2:X" & Sheet2.Rows.Count).Clear
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
07-03-2019 02:49 AM
Variant with formulas for such sample
=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.
and drag to the right and down.
If you have version of Excel with coming dynamic arrays, when
and drag to the right
07-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.
07-03-2019 08:12 AM