+ Reply to Thread
Results 1 to 3 of 3

Query to edit raw data layout

  1. #1
    Forum Contributor
    Join Date
    03-12-2012
    Location
    Singapore
    MS-Off Ver
    MS 365
    Posts
    532

    Query to edit raw data layout

    Hi, i extract the bank statement and it show the Service Charge one Row below the transaction.
    Example, Row2 have a $3000 transaction with Reference 999. Then Row3 have a $5 service charge with Reference 999
    I wan this $5 to appear in Row 2 (additional column) with Svc Charge as header. And another column to Add them up.

    How can this be done?
    Refer attached.
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,062

    Re: Query to edit raw data layout

    1. Your output data doesn't reflect your input data - in C2 you've added the word "TOKYO" - this is impossible to replicate with a formula unless you know what the data should be.

    2. You've removed CASHIERS ORDER from column D - in the formula below I haven't.

    3. To solve your problem

    In your 'What I wan' sheet

    in A2

    =IFERROR(INDEX('Raw Data'!$A$2:$E$1000,AGGREGATE(15,6,ROW($A$2:$E$1000)/(('Raw Data'!$D$2:$D$1000<>"SVC CHG CO")),ROWS(A$2:A2))-(2-1),COLUMN()),"")
    copy across to column E

    in F2
    =IFERROR(INDEX('Raw Data'!$B$2:$B$1000,AGGREGATE(15,6,ROW($A$2:$B$1000)/(('Raw Data'!$D$2:$D$1000="SVC CHG CO")*('Raw Data'!E$2:E$1000=E2)),ROWS(F$2:F2))-(2-1),1),"")

    in G2
    =B2+F2

    format the columns as required, change dates to look like dates etc

    copy from A2:G2 down for at least as many rows as you have in 'Raw Data' column A
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.

  3. #3
    Forum Expert WideBoyDixon's Avatar
    Join Date
    10-03-2016
    Location
    Sheffield, UK
    MS-Off Ver
    365
    Posts
    2,182

    Re: Query to edit raw data layout

    It's possible without using a helper column but much easier with a helper column - column H in the attached. You can change the font colour to white or hide the column.

    WBD
    Attached Files Attached Files
    Office 365 on Windows 11, looking for rep!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Query doesn't preserve layout
    By FinFra in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-20-2017, 04:17 PM
  2. Edit Web Query - get data from web
    By ag273n in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 05-10-2014, 12:16 PM
  3. Convert data from column layout to row layout
    By maacmaac in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-28-2010, 03:28 PM
  4. How to do Edit Query from Import External Data.
    By Excel General Questions in forum Excel General
    Replies: 2
    Last Post: 04-13-2009, 08:33 PM
  5. [SOLVED] Query - Data Layout
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 06-19-2006, 07:00 AM
  6. Excel new/edit External Data Query hangs
    By Melissa in forum Excel General
    Replies: 0
    Last Post: 04-19-2006, 02:45 PM
  7. [SOLVED] Edit Query from Excel will not open query in MSQuery
    By Michelle in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-21-2005, 12:06 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1