Results 1 to 3 of 3

Thread: Shift specific cells of specific rows only

  1. #1
    Seeker RoguePotatoes's Avatar
    Join Date
    Jul 2019
    Posts
    5
    Articles
    0
    Excel Version
    2019 (x64)

    Shift specific cells of specific rows only



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Using Get from Folder I have consolidated about 2,000 Work Order forms that were made in excel. The overall look of the form is generally the same but unfortunately the template has a few variations and the data isn't always put in the exact same cell, but its always in the same general area, usually within a cell or two away. So I transformed one worksheet into a single row of data and used it as a function to create a query with 38 columns and over 2,000 rows, each row being a single form worth of data (including titles and some other info I don't need). But of course some of the data doesn't line up with the rest.

    My question is, is there a way to move or rearrange the columns of specific rows only, without affecting the rows whose data is in the right place/order?

    I have tried writing my own M code to do this with records and transforming fields. I have tried selecting and rearranging columns:

    Code:
        Custom1 = Table.ReorderColumns( Table.SelectRows( #"Filtered Rows", each [Column1] = "CUSTOMER:"), {"Column2", "Column1"}),
        #"Renamed Columns" = Table.RenameColumns(Custom1,{{"Column1", "Column2"}, {"Column2", "Column1"}}),
        #"Append" = Custom1 & Table.SelectRows( #"Filtered Rows", each [Column1] <> "CUSTOMER:"),
    but I can't get anything to work so far. I can upload an example if that helps, I'd just have to change sensitive data.

  2. #2
    Seeker BernardBrussels's Avatar
    Join Date
    Mar 2019
    Posts
    9
    Articles
    0
    Excel Version
    excel 365
    Hi RoguePatatoes,

    Not realy knowing it all...

    but I would:
    Have the first query that import all rows. & add a column to indicate in which set the row belongs
    create 2 separated queries to handle the 2 sets & transforme them to the same layout
    merge one of the set into the second.

  3. #3
    Administrator AliGW's Avatar
    Join Date
    Nov 2015
    Location
    Ipswich, Suffolk, England
    Posts
    1,235
    Articles
    0
    Excel Version
    Office 365 Subscription
    We'd need to see a sample workbook to help further.
    Ali
    Enthusiastic self-taught user of MS Excel!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •