Copy Data to Other Sheets' Columns Based on Criteria

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

@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).


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


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".

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies