SOLVED
Home

Copy Data to Other Sheets' Columns Based on Values

%3CLINGO-SUB%20id%3D%22lingo-sub-734254%22%20slang%3D%22en-US%22%3ECopy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-734254%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20been%20searching%20like%20crazy%20but%20struggling%20to%20find%20a%20solution%20that%20specifically%20fits%20what%20I%20am%20aiming%20to%20achieve.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirst%20week%20of%20every%20month%2C%20I%20hope%20to%20walk%20into%20a%20patch-cycle%20meeting%20with%20an%20excel%20spreadsheet%20containing%20a%20list%20of%20servers.%20Next%20to%20that%20list%20of%20servers%20is%20another%20column%20with%20the%20'week.day'%20so%20when%20we%20get%20the%20server%20owner%20to%20i.e.%20agree%20for%20patching%20on%20week%201%20day%203%20that%20column%20would%20have%201.3%20populated%20in%20there.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20198px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121898iEB56CF1078E1502A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22WeekServ.jpg%22%20title%3D%22WeekServ.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EBecause%20of%20the%20nature%20that%20we%20perform%20our%20updates%2C%20I%20need%20another%20sheet%20to%20have%20headers%20for%20each%20of%20the%20'Week'%20values%20(which%20can%20be%20pre-populated)%20and%20populate%20the%20cells%20below%20with%20each%20server%20that%20is%20marked%20for%20that%20week.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20193px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F121899i6D95CF1547339440%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22weekpln.jpg%22%20title%3D%22weekpln.jpg%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EI%20have%20tried%20using%20tools%20such%20as%20vlookup%20and%20some%20methods%20using%20%3Dindex%20but%20struggling%20to%20make%20any%20progress.%20I%20confess%20to%20being%20very%20new%20to%20complex%20Excel%20formulas%20so%20wondering%20if%20anyone%20has%20suggestions%20they%20could%20share%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20cannot%20deviate%20too%20greatly%20from%20the%20format%20above%20as%20it%20forms%20the%20basis%20of%20a%20macro-export%20to%20text%20file%20that%20I%20have%20which%20creates%20a%20text%20file%20per-week%20(based%20upon%20the%20header%20value)%20each%20of%20which%20contain%20the%20servers%20for%20that%20week%2Fday.%20Any%20help%20would%20be%20greatly%20appreciated!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-734254%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-735586%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-735586%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369893%22%20target%3D%22_blank%22%3E%40WilderBog%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3Eyou%20can%20do%20this%20with%20the%20following%20macro%2C%20see%20attachment.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3ESub%20TransferData()%3CBR%20%2F%3EDim%20lngRow%20As%20Long%3CBR%20%2F%3EDim%20lngRowmax%20As%20Long%3CBR%20%2F%3EDim%20rngFind%20As%20Range%3CBR%20%2F%3EDim%20lngRowMaxT%20As%20Long%3CBR%20%2F%3E%3CBR%20%2F%3ESheet2.Range(%22A2%3AX%22%20%26amp%3B%20Sheet2.Rows.Count).Clear%3CBR%20%2F%3E%3CBR%20%2F%3EWith%20Sheet1%3CBR%20%2F%3ElngRowmax%20%3D%20.Range(%22a%22%20%26amp%3B%20.Rows.Count).End(xlUp).Row%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20lngRow%20%3D%201%20To%20lngRowmax%3CBR%20%2F%3E%3CBR%20%2F%3ESet%20rngFind%20%3D%20Sheet2.Rows(1).Find(what%3A%3D.Range(%22A%22%20%26amp%3B%20lngRow).Value%2C%20lookat%3A%3DxlWhole)%3CBR%20%2F%3EIf%20Not%20rngFind%20Is%20Nothing%20Then%3CBR%20%2F%3ElngRowMaxT%20%3D%20Sheet2.Cells(Sheet2.Rows.Count%2C%20rngFind.Column).End(xlUp).Row%20%2B%201%3CBR%20%2F%3ESheet2.Cells(lngRowMaxT%2C%20rngFind.Column).Value%20%3D%20.Range(%22B%22%20%26amp%3B%20lngRow).Value%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20If%3CBR%20%2F%3ENext%20lngRow%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20With%3CBR%20%2F%3E%3CBR%20%2F%3EEnd%20Sub%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%3C%2FP%3E%3CP%3EBernd%3C%2FP%3E%3CP%3E%3CA%20title%3D%22VBA-Tanker%22%20href%3D%22https%3A%2F%2Fvba-tanker.com%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EThe%20vba-Tanker%20-%20a%20database%20full%20of%20macros%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736028%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736028%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F369893%22%20target%3D%22_blank%22%3E%40WilderBog%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EVariant%20with%20formulas%20for%20such%20sample%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20544px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F122058iCCE57AB717F8B68E%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20alt%3D%22image.png%22%20title%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIn%20I2%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Table1%5BWeek%5D%2CAGGREGATE(15%2C6%2C1%2F(COUNTIF(%24H%242%3AH%242%2CTable1%5BWeek%5D)%3D0)*(ROW(Table1%5BWeek%5D)-ROW(Table1%5B%5B%23Headers%5D%2C%5BWeek%5D%5D))%2C%201%20))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20to%20the%20right%20till%20empty%20cells%20appear.%3C%2FP%3E%0A%3CP%3EIn%20I3%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Table1%5BServer%5D%2CAGGREGATE(15%2C6%2C1%2F(Table1%5BWeek%5D%3DI%242)*(ROW(Table1%5BWeek%5D)-ROW(Table1%5B%5B%23Headers%5D%2C%5BWeek%5D%5D))%2C(ROW()-ROW(I%242))))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20to%20the%20right%20and%20down.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20you%20have%20version%20of%20Excel%20with%20coming%20dynamic%20arrays%2C%20when%3C%2FP%3E%0A%3CP%3Ein%20E2%3C%2FP%3E%0A%3CPRE%3E%3DTRANSPOSE(UNIQUE(Table1%5BWeek%5D))%3C%2FPRE%3E%0A%3CP%3Ein%20E3%3C%2FP%3E%0A%3CPRE%3E%3DFILTER(Table1%5BServer%5D%2CTable1%5BWeek%5D%3DE%242)%3C%2FPRE%3E%0A%3CP%3Eand%20drag%20to%20the%20right%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736543%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736543%22%20slang%3D%22en-US%22%3EHi%20Sergei%2C%3CBR%20%2F%3EThank%20you%20for%20this%2C%20I%20was%20a%20little%20puzzled%20when%20I%20changed%20the%20'week'%20number%20against%20Server1%20and%20it%20broke%20the%20results%20but%20i%20suspect%20that%20is%20my%20fault%20in%20the%20way%20I%20have%20described%20the%20requirements.%3CBR%20%2F%3E%3CBR%20%2F%3EThe%20key%20identifier%20is%20the%20Server%20which%20i%20would%20usually%20have%20as%20a%20field%20to%20the%20left%20but%20because%20I%20was%20working%20with%20Vlookups%20i'd%20moved%20the%20'Week'%20to%20the%20left%20column%20in%20order%20to%20allow%20vlookup%20to%20function%20properly.%3CBR%20%2F%3E%3CBR%20%2F%3EEssentially%2C%20the%20result%20achieved%20by%20Bernd's%20method%20was%20spot%20on%20so%20i'll%20amalgamate%20his%20code%20into%20my%20spreadsheet%20but%20thank%20you%20for%20taking%20the%20time%20to%20help%20me!%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-736540%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-736540%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F347933%22%20target%3D%22_blank%22%3E%40Berndvbatanker%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EExactly%20what%20I%20was%20looking%20for!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20for%20this%20Bernd%2C%20that%20makes%20this%20process%20a%20great%20deal%20easier%2C%20now%20to%20apply%20your%20solution%20to%20my%20spreadsheet.%3C%2FP%3E%3C%2FLINGO-BODY%3E
WilderBog
New Contributor

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.

 

WeekServ.jpg

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.

 

weekpln.jpg

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!

 

4 Replies
Solution

@WilderBog 

Hi,

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

The vba-Tanker - a database full of macros

@WilderBog 

Variant with formulas for such sample

image.png

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

@Berndvbatanker 

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.

Hi Sergei,
Thank you for this, I was a little puzzled when I changed the 'week' number against Server1 and it broke the results but i suspect that is my fault in the way I have described the requirements.

The key identifier is the Server which i would usually have as a field to the left but because I was working with Vlookups i'd moved the 'Week' to the left column in order to allow vlookup to function properly.

Essentially, the result achieved by Bernd's method was spot on so i'll amalgamate his code into my spreadsheet but thank you for taking the time to help me!
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
22 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
PacketMon Components are not loading in WAC 1909
HotCakeX in Windows Admin Center on
2 Replies