05-30-2019 01:03 PM
05-30-2019 01:03 PM
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.
05-30-2019 01:16 PMSolution
The easiest is with Power Query - query source data, filter and land result back. Sample attached.
05-30-2019 02:08 PM - edited 05-30-2019 02:10 PM
@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).
05-30-2019 02:34 PM
Thomas, to show update click on ribbon Data->Refresh All. Or right click on resulting table and Refresh.
05-30-2019 03:46 PM
@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.
05-31-2019 02:22 PM
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".