SOLVED
Home

Copy Data to Other Sheets' Columns Based on Criteria

%3CLINGO-SUB%20id%3D%22lingo-sub-659153%22%20slang%3D%22en-US%22%3ECopy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659153%22%20slang%3D%22en-US%22%3E%3CP%3EApologies%20if%20this%20is%20answered.%26nbsp%3B%20The%20search%20I%20did%20found%20similar%20items%20but%20not%20the%20result%20I'm%20looking%20for.%26nbsp%3B%20For%20simplicity%2C%20let's%20say%20I%20have%20three%20columns.%26nbsp%3B%20The%20first%20two%20are%20data%20and%20the%20third%20is%20where%20I%20would%20mark%20with%20an%20'X'%20that%20I%20want%20that%20data%20transposed.%26nbsp%3B%20I%20want%20all%20data%20rows%20with%20an%20x%20in%20that%20third%20column%20to%20be%20copied%20to%20another%20worksheet%2C%20skipping%20lines%20without%20the%20X%20(but%20not%20having%20a%20blank%2C%20just%20moving%20onto%20the%20next%20X).%26nbsp%3B%20First%20page%20of%20the%20attachment%20is%20the%20data%20sheet.%26nbsp%3B%20Second%20pageis%20how%20this%20would%20transpose%20correctly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-659153%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-659171%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659171%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F107985%22%20target%3D%22_blank%22%3E%40Thomas%20Fallon%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EHi%20Thomas%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20easiest%20is%20with%20Power%20Query%20-%20query%20source%20data%2C%20filter%20and%20land%20result%20back.%20Sample%20attached.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659257%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659257%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you%20Sergei%20-%20it%20does%20not%20appear%20that%20if%20I%20make%20a%20change%20to%20the%20original%20table%2C%20that%20the%20queries%20update%3F%26nbsp%3B%20(note%20I%20have%20kept%20the%20queries%20in%20the%20same%20Workbook%2C%20on%20a%20separate%20Sheet).%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659326%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659326%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F107985%22%20target%3D%22_blank%22%3E%40Thomas%20Fallon%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThomas%2C%20to%20show%20update%20click%20on%20ribbon%20Data-%26gt%3BRefresh%20All.%20Or%20right%20click%20on%20resulting%20table%20and%20Refresh.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-659443%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-659443%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3BThank%20you.%26nbsp%3B%20That%20does%20work%2C%20but%20I%20was%20hoping%20for%20an%20automatic%20update.%26nbsp%3B%20The%20people%20in%20my%20office%20will%20not%20know%20to%20click%20refresh%20(and%20in%20fact%20I'm%20keeping%20them%20out%20of%20the%20excel%20so%20they%20will%20only%20see%20what%20is%20referenced%20into%20our%20AutoCAD%20file%20from%20excel).%26nbsp%3B%20But%20I%20can%20make%20do%20with%20this%20if%20there%20is%20no%20easy%20way%20to%20auto%20update.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-661749%22%20slang%3D%22en-US%22%3ERe%3A%20Copy%20Data%20to%20Other%20Sheets'%20Columns%20Based%20on%20Criteria%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-661749%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F107985%22%20target%3D%22_blank%22%3E%40Thomas%20Fallon%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThomas%2C%20there%20is%20no%20autorefresh%2C%20but%20you%20may%20right%20click%20on%20query%2C%20open%20properties%20and%20here%20set%20refreshing%20on%20file%20open%20and%2For%20every%20X%20minutes%20(when%20file%20is%20opened).%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20465px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116802iCAB56B8733BA9D18%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%3EAlternatively%20you%20may%20use%20formula%20like%3C%2FP%3E%0A%3CPRE%3E%3DIFERROR(INDEX(Table1%5BItem%5D%2CAGGREGATE(15%2C6%2C1%2F(Table1%5BLower%20Flat%20Plan%5D%3D%22X%22)*(ROW(Table1%5BItem%5D)-ROW(Table1%5B%5B%23Headers%5D%2C%5BItem%5D%5D))%2CROW()-ROW(%24G%241)))%2C%22%22)%3C%2FPRE%3E%0A%3CP%3EFormula%20is%20in%20G2%20in%20attached%20file%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20327px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116803iFC55DC230024566C%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%3Edrag%20it%20down%20till%20max%20of%20your%20range.%20Empty%20cells%20appear%20if%20no%20match.%3C%2FP%3E%0A%3CP%3EFormula%20uses%20structured%20references.%20If%20your%20source%20is%20the%20range%2C%20not%20table%2C%20it'll%20be%20similar.%3C%2FP%3E%0A%3CP%3EWith%20this%20it'll%20be%20updated%20as%20you%20add%2Fremove%20%22X%22.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Thomas Fallon
New Contributor

Apologies if this is answered.  The search I did found similar items but not the result I'm looking for.  For simplicity, let's say I have three columns.  The first two are data and the third is where I would mark with an 'X' that I want that data transposed.  I want all data rows with an x in that third column to be copied to another worksheet, skipping lines without the X (but not having a blank, just moving onto the next X).  First page of the attachment is the data sheet.  Second pageis how this would transpose correctly.

5 Replies
Solution

@Thomas Fallon ,

 

Hi Thomas,

 

The easiest is with Power Query - query source data, filter and land result back. Sample attached.

@Sergei Baklan Thank you Sergei - it does not appear that if I make a change to the original table, that the queries update?  (note I have kept the queries in the same Workbook, on a separate Sheet).

@Thomas Fallon ,

 

Thomas, to show update click on ribbon Data->Refresh All. Or right click on resulting table and Refresh.

@Sergei Baklan Thank you.  That does work, but I was hoping for an automatic update.  The people in my office will not know to click refresh (and in fact I'm keeping them out of the excel so they will only see what is referenced into our AutoCAD file from excel).  But I can make do with this if there is no easy way to auto update.  

@Thomas Fallon 

Thomas, there is no autorefresh, but you may right click on query, open properties and here set refreshing on file open and/or every X minutes (when file is opened).

image.png

Alternatively you may use formula like

=IFERROR(INDEX(Table1[Item],AGGREGATE(15,6,1/(Table1[Lower Flat Plan]="X")*(ROW(Table1[Item])-ROW(Table1[[#Headers],[Item]])),ROW()-ROW($G$1))),"")

Formula is in G2 in attached file

image.png

drag it down till max of your range. Empty cells appear if no match.

Formula uses structured references. If your source is the range, not table, it'll be similar.

With this it'll be updated as you add/remove "X".