Forum Discussion

RanzieJ's avatar
RanzieJ
Copper Contributor
Aug 11, 2022
Solved

EXCEL - HOW TO MOVE MULTIPLE DATA (ONLY) FROM COLUMN TO OTHER COLUMN BLANK

  Good day, can i ask for help regarding my problem in excel?  i want to move/cut data from "Credit account" to the left  "Debit Account" . I have a hundred transactions 😞 i cant move it one by ...
excel
  • Lorenzo's avatar
    Lorenzo
    Aug 11, 2022

    Hi RanzieJ 

     

    https://support.microsoft.com/en-us/office/merge-columns-power-query-80ec9e1e-1eb6-4048-b500-d5d42d9f0a8d with Power Query:

Harun24HR's avatar
Harun24HR
Bronze Contributor
Aug 11, 2022

RanzieJ You need VBA coding to cut/move data. Give a try on below sub. Assuming you Credit Account column is B. You have to adjust Range("B2:B200") for you real case.

 

Sub MoveData()
Dim rng As Range

    For Each rng In Range("B2:B200")
        If rng <> "" Then
            rng.Offset(, -1) = rng
            rng.Clear
        End If
    Next rng

End Sub

 

 

  • RanzieJ's avatar
    RanzieJ
    Copper Contributor
    Aug 11, 2022
    Woahh, i think this is too advance. Can you teach me step by step, please? 😞
    • sivakumarrj's avatar
      sivakumarrj
      Brass Contributor
      Aug 11, 2022

      RanzieJ 

      Simply select Column B & Column C,

      Under Data heading  Click filter button

      Select Column B with Blanks

      Next Select Column C with Cash or Cash in Hand

       

      Now it will display with rows of Cash 

      Type formula in Cell B5 = C5(Cash)

      then copy formula and then do special paste with values

      after that you delete the b column

       

       

    • Lorenzo's avatar
      Lorenzo
      Silver Contributor
      Aug 11, 2022

      RanzieJ 

       

      This isn't advanced at all but pretty classic Power Query transformation

       

      #1 Format your https://www.bing.com/ck/a?!&&p=efd4676d014d09bbJmltdHM9MTY2MDE5NDk2NiZpZ3VpZD03MDFhMDFiZS05MDQ1LTQ4Y2QtYjllMC0yMjdkYzIxNzNhNjAmaW5zaWQ9NTE2Nw&ptn=3&hsh=3&fclid=b39e3db3-1934-11ed-a85d-9175da19d031&u=a1aHR0cHM6Ly9zdXBwb3J0Lm1pY3Jvc29mdC5jb20vZW4tdXMvb2ZmaWNlL2Zvcm1hdC1hbi1leGNlbC10YWJsZS02Nzg5NjE5Zi1jODg5LTQ5NWMtOTljMi0yZjk3MWMwZTIzNzA&ntb=1:

      #2 Go to Data (tab) > From Table/Range (Power Query editor opens)

      #3 Go to Transform tab

      #4 Select columns [DEBIT ACCOUNT] & [CREDIT ACCOUNT] > Merge Columns

      #5 By default the new column name is "Merged". Change this with i.e. DEBIT/CREDIT ACCOUNT > OK

      #6 File > Close & Load

      • RanzieJ's avatar
        RanzieJ
        Copper Contributor
        Aug 18, 2022
        Got it, Thank you so much! Godbless!

Resources