Rearrange Data Question

[DevonH]

I am wondering what the best and easiest method is to rearrange data into a better format/table. For example, when I copy and paste data from a webpage, it gives the following format:

31-Dec-21
Interest Deposit
$6
$7
2-Dec-21
Mobile Deposit
$5
$4
30-Nov-21
Interest Deposit
$3
$2
22-Nov-21
Mobile Deposit
$1
$.01

But instead I would like to rearrange it in the following format:

31-Dec-21​	Interest Deposit	$6​	$7​
2-Dec-21​	Mobile Deposit	$5​	$4​
30-Nov-21​	Interest Deposit	$3​	$2​
22-Nov-21​	Mobile Deposit	$1​	$.01​

Thank you!

 

[DanteAmor]

The data should start in cell A2.
Put the formula in C2 and copy to the right and down:

varios 31ene2022.xlsm
	A	B	C	D	E	F
1
2	31-Dec-21		31-Dec-21	Interest Deposit	$6.00	$7.00
3	Interest Deposit		2-Dec-21	Mobile Deposit	$5.00	$4.00
4	$6.00		30-nov-21	Interest Deposit	$3.00	$2.00
5	$7.00		22-nov-21	Mobile Deposit	$1.00	$0.01
6	2-Dec-21
7	Mobile Deposit
8	$5.00
9	$4.00
10	30-nov-21
11	Interest Deposit
12	$3.00
13	$2.00
14	22-nov-21
15	Mobile Deposit
16	$1.00
17	$0.01
Hoja3

Cell Formulas
Range	Formula
C2:F5	C2	=OFFSET($A2,(COLUMNS($B1:B1)-1)+((ROWS(B$1:B1)-1)*3),0)

 

[DevonH]

Thank you very much - that was very helpful!

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.

 

[alansidman]

An alternative solution is with Power Query

Book1
	A	B	C	D	E
1	Column1		Date2	Deposit	Amount
2	31-Dec-21		12/2/2021	Interest Deposit	6
3	Interest Deposit		12/2/2021	Interest Deposit	7
4	$6		11/30/2021	Mobile Deposit	5
5	$7		11/30/2021	Mobile Deposit	4
6	2-Dec-21		11/22/2021	Interest Deposit	3
7	Mobile Deposit		11/22/2021	Interest Deposit	2
8	$5		11/22/2021	Mobile Deposit	1
9	$4		11/22/2021	Mobile Deposit	0.01
10	30-Nov-21
11	Interest Deposit
12	$3
13	$2
14	22-Nov-21
15	Mobile Deposit
16	$1
17	$0.01
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Promoted Headers" = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
    #"Added Custom" = Table.AddColumn(#"Promoted Headers", "Deposit", each if Text.Contains([#"31-Dec-21"],"Deposit") then [#"31-Dec-21"] else null),
    #"Replaced Errors" = Table.ReplaceErrorValues(#"Added Custom", {{"Deposit", null}}),
    #"Filled Down" = Table.FillDown(#"Replaced Errors",{"Deposit"}),
    #"Added Custom1" = Table.AddColumn(#"Filled Down", "Date", each if (try DateTime.ToText([#"31-Dec-21"],"yyyy-mm-dd") otherwise "No") = "No" then "No" else "IsDate"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date2", each if([Date] = "IsDate") then [#"31-Dec-21"] else null),
    #"Filled Up" = Table.FillUp(#"Added Custom2",{"Date2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Up",{{"Date2", type date}}),
    #"Filled Down1" = Table.FillDown(#"Changed Type",{"Date2"}),
    #"Filtered Rows" = Table.SelectRows(#"Filled Down1", each ([Date] = "No")),
    #"Added Custom3" = Table.AddColumn(#"Filtered Rows", "Amount", each try Number.From([#"31-Dec-21"]) is number otherwise false),
    #"Filtered Rows1" = Table.SelectRows(#"Added Custom3", each ([Amount] = true)),
    #"Removed Other Columns" = Table.SelectColumns(#"Filtered Rows1",{"31-Dec-21", "Deposit", "Date2"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Removed Other Columns",{"Date2", "Deposit", "31-Dec-21"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"31-Dec-21", "Amount"}})
in
    #"Renamed Columns"