SOLVED

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

New Contributor
 

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

 

Thank You!

 

 

RanzieJ_1-1660183172040.png

 

7 Replies

@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

 

 

best response confirmed by RanzieJ (New Contributor)
Solution

Hi @RanzieJ 

 

Merge columns with Power Query:

_Screenshot.png

Woahh, i think this is too advance. Can you teach me step by step, please?

@RanzieJ 

 

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

 

#1 Format your data formatted as Table:

_Screenshot.png

#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

_Screenshot2.png

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

_Screenshot3.png

#6 File > Close & Load

@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

sivakumarrj_0-1660198916776.png

 

 

@RanzieJ 

 

Hi there.

 

Filter the credit account and choose the range like this:

Sekoleyte_1-1660199113281.png

Then fill them left like that:

 

Sekoleyte_0-1660199064914.png

 

Got it, Thank you so much! Godbless!