Row and column data manipulation

[Henoclaide]

What is easy and efficient way to move supplier name to new column in accordance where is data .See image as example

Accounts Payable Age Analysis_20211215_144233 - Copy.xlsx
	A	B	C	D	E	F	G	H	I	J
5	Date	Reference	180 Days	150 Days	120 Days	90 Days	60 Days	30 Days	Current	Balance
6	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)
7	16/08/2021	BCI-MZN Transf				-1,359.12				-1,359.12
8	17/08/2021	BCI-MZN Transf				-33,801.10				-35,160.22
9	08/09/2021	BCI-MZN Transf					-1,064.69			-36,224.91
10	23/09/2021	BCI-MZN Transf					-2,744.76			-38,969.67
11	01/11/2021	2815							750.00	-38,219.67
12	09/11/2021	BCI-MZN Transf							-78,463.00	-116,682.67
13	17/11/2021	2853							11,726.88	-104,955.79
14	23/11/2021	NED-MZN Transf							-30,931.23	-135,887.02
15	26/11/2021	2866							13,940.02	-121,947.00
16	Total for ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)					-35,160.22	-3,809.45		-82,977.33	-121,947.00
17	ACPE01 (AC PECAS, Lda)
18	22/11/2021	CX-MZN 960							-819.00	-819.00
19	Total for ACPE01 (AC PECAS, Lda)								-819.00	-819.00
20	ADIL01 (Adilson da Silva)
21	30/11/2021	975							107,640.00	107,640.00
22	30/11/2021	976							23,400.00	131,040.00
23	Total for ADIL01 (Adilson da Silva)								131,040.00	131,040.00
24	AFRI04 (Afri-Mechs Ltd)
25	12/01/2021	20	60,117.42							60,117.42
26	30/04/2021	29	648,226.80							708,344.22
27	Total for AFRI04 (Afri-Mechs Ltd)		708,344.22							708,344.22
28	AFRO01 (AFROX Mocambique Lda)
29	Total for AFRO01 (AFROX Mocambique Lda)					8,008.07				8,008.07
30	AFST01 (Advanced Fire Supression Technologies)
31	02/08/2021	INV02861			17,828.46					17,828.46
32	Total for AFST01 (Advanced Fire Supression Technologies)				17,828.46					17,828.46
33	ALIE01 (Alie Investiment Lda)
34	18/09/2021	306					30.24			30.24
35	04/10/2021	311						560,000.10		560,030.34
36	Total for ALIE01 (Alie Investiment Lda)						30.24	560,000.10		560,030.34
37	AMEL01 (Amelia Z.T.S Rendicao)
38	05/11/2021	3185							40.00	40.00
39	Total for AMEL01 (Amelia Z.T.S Rendicao)								40.00	40.00
40	ANIF01 (Anif Comercial )
41	04/09/2021	2465					0.01			0.01
42	Total for ANIF01 (Anif Comercial )						0.01			0.01
43	ASSO01 (Associacao Arteba)
44	22/09/2021	BU-MZN Transf					-12,000.00			-12,000.00
45	02/10/2021	89						30,000.00		18,000.00
46	Total for ASSO01 (Associacao Arteba)						-12,000.00	30,000.00		18,000.00
47	AUTO16 (Auto Sueco Mocambique (SA))
48	25/10/2021	A3/2021/7311103000						47,809.63		47,809.63
49	25/10/2021	A3/2021/7311103001						148,778.67		196,588.30
50	Total for AUTO16 (Auto Sueco Mocambique (SA))							196,588.30		196,588.30
51	AUTO20 (Auto Stop Amirana, Lda)
52	06/07/2021	BU-MZN Transf			-228,000.00					-228,000.00
53	02/08/2021	MOZA-MZN Transf			-228,000.00					-456,000.00
54	Total for AUTO20 (Auto Stop Amirana, Lda)				-456,000.00					-456,000.00
55	AVAN01 (Avani Pemba Beach Hotel & Spa)
56	08/11/2021	165145/2							3,290.00	3,290.00
57	08/11/2021	165647/1							154,879.96	158,169.96
58	Total for AVAN01 (Avani Pemba Beach Hotel & Spa)								158,169.96	158,169.96
Accounts Payable Age Analysis_2

 

[SpillerBD]

I see a Pivot Table. However, I'm guessing this is some output from another system as a Text file brought to Excel or an Excel file created by another system/software.
Preference is to Use PowerQuery for such clean up.
However, a manula method that's not too bad once you get used to all the bits....
Filter Column A for items beginning with "Total" and Delete.
Insert a New Column at A. Set the Header to "Supplier Name"
=IF(JRow=""),BRow,"")
Fill formula down to end of data.

Then a magic Trick. To fill down the Supplier Names....
Select Column A, Copy, Paste Values. You cannot have the formulas.
Select the range, CTRL+END, Home, SHIFT+CTRL+HOME
Press F5. In the Find Window, Choose the Blank Cells.
You should have multiple cells selected and like Cell A3 is activated. You will Type a formula to reference the cell above the current active cell, "=A2", the CTRL+ENTER.
That should have each blank cell reference the previous-above cell by formula, if the cell was blank. You need to convert the column to hard values using COPY, PASTE SPEACIAL/VALUES.

 

[Alex Blakenburg]

Assuming you don't want to use Power Query or VBA,
I prefer to use a template and rely on a pivot for the actual report.
So I would normally put the formulas I have here to the right of the original data, then rely on the pivot to bring the names to the left.

If you are happy to use filters to filter out the rows with no Data then you can still use this a template, to refresh delete the data on the right and leave the formulas in place. Copy in the new data into Column C and just make sure the formulas cover the whole range.

If you are going to convert the formulas to values each time then either store the formulas on another sheet or set the formulas up in the Name Range Manager.

20211226 Fill Creditor Name Down.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	Data Row YN	Date	Reference	180 Days	150 Days	120 Days	90 Days	60 Days	30 Days	Current	Balance
2	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	No	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)
3	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	Yes	16/08/2021	BCI-MZN Transf				-1359.12				-1359.12
4	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	Yes	17/08/2021	BCI-MZN Transf				-33801.1				-35160.22
5	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	Yes	8/09/2021	BCI-MZN Transf					-1064.69			-36224.91
6	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	Yes	23/09/2021	BCI-MZN Transf					-2744.76			-38969.67
7	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	Yes	1/11/2021	2815							750	-38219.67
8	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	Yes	9/11/2021	BCI-MZN Transf							-78463	-116682.67
9	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	Yes	17/11/2021	2853							11726.88	-104955.79
10	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	Yes	23/11/2021	NED-MZN Transf							-30931.23	-135887.02
11	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	Yes	26/11/2021	2866							13940.02	-121947
12	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	Yes	Total for ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)					-35160.22	-3809.45		-82977.33	-121947
13	ACPE01 (AC PECAS, Lda)	No	ACPE01 (AC PECAS, Lda)
14	ACPE01 (AC PECAS, Lda)	Yes	22/11/2021	CX-MZN 960							-819	-819
15	ACPE01 (AC PECAS, Lda)	Yes	Total for ACPE01 (AC PECAS, Lda)								-819	-819
16	ADIL01 (Adilson da Silva)	No	ADIL01 (Adilson da Silva)
17	ADIL01 (Adilson da Silva)	Yes	30/11/2021	975							107640	107640
18	ADIL01 (Adilson da Silva)	Yes	30/11/2021	976							23400	131040
19	ADIL01 (Adilson da Silva)	Yes	Total for ADIL01 (Adilson da Silva)								131040	131040
Formatted Col A vrs 2

Cell Formulas
Range	Formula
A2:A19	A2	=IF(AND(NOT(ISERROR(FIND("(",C2))),LEFT(C2,5)<>"Total"),C2,A1)
B2:B19	B2	=IF(L2="","No","Yes")

 

[Henoclaide]

I see a Pivot Table. However, I'm guessing this is some output from another system as a Text file brought to Excel or an Excel file created by another system/software.
Preference is to Use PowerQuery for such clean up.
However, a manula method that's not too bad once you get used to all the bits....
Filter Column A for items beginning with "Total" and Delete.
Insert a New Column at A. Set the Header to "Supplier Name"
=IF(JRow=""),BRow,"")
Fill formula down to end of data.

Then a magic Trick. To fill down the Supplier Names....
Select Column A, Copy, Paste Values. You cannot have the formulas.
Select the range, CTRL+END, Home, SHIFT+CTRL+HOME
Press F5. In the Find Window, Choose the Blank Cells.
You should have multiple cells selected and like Cell A3 is activated. You will Type a formula to reference the cell above the current active cell, "=A2", the CTRL+ENTER.
That should have each blank cell reference the previous-above cell by formula, if the cell was blank. You need to convert the column to hard values using COPY, PASTE SPEACIAL/VALUES.

Hello SpillerBD
Thank you for prompt reply.
It is excel extracted from accounting software.
I have tried above instruction, the formula is not working. I taught the problem was the parenthesis that is missing but even did not work.
If I have to use Power query, what would I have to do?
Regards,
Henocláide

 

[Henoclaide]

Thank you, worked. If I have to use power query, what would I have to do?

 

[SpillerBD]

Thank you, worked. If I have to use power query, what would I have to do?

PowerQuery is pretty easy to learn. I always recommend Mike Girvin (ExcelIsFun) on you Tube for long instructions. There are others, but he is all around one of the best.
It looks like you have the invoice or due date from the accounting software and then that same software is putting it in the appropriate aging bucket.
I would calculate the days and then use the PivotTable functions to put in the correct bucket,

The Excel file must be in true XLSX format for Power Query to work with. That may be your first hurdle. A Text file can be just as easy.
Book resources would be the new(updated) from Puls and Escobar, Master Your Data with Power Query in Excel and PowerBI

 

[Alex Blakenburg]

Your case is pretty straight forward in Power Query (PQ) and doesn't require any hard coding.
Just as a test run, I converted your data into a table called tblAgedCred.
(You can let PQ do that but I don't find the table being called Table1 all that helpful)
I then used the PQ interface to perform the following steps.

It generates the M Code below. If you wanted to you could paste that into a blank PQ query and if you use my table name tblAgedCred it should run without doing anything else.

let
    Source = Excel.CurrentWorkbook(){[Name="tblAgedCred"]}[Content],
    AddedColumnName = Table.AddColumn(Source, "Supplier Name", each if [Balance] = null then [Date] else null),
    #"Filled Down Name" = Table.FillDown(AddedColumnName,{"Supplier Name"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Filled Down Name",{{"Date", type date}, {"Reference", type text}, {"180 Days", Currency.Type}, {"150 Days", Currency.Type}, {"120 Days", Currency.Type}, {"90 Days", Currency.Type}, {"60 Days", Currency.Type}, {"30 Days", Currency.Type}, {"Current", Currency.Type}, {"Balance", Currency.Type}, {"Supplier Name", type text}}),
    #"Removed Errors Date" = Table.RemoveRowsWithErrors(#"Changed Type", {"Date"}),
    #"Reordered Columns Name" = Table.ReorderColumns(#"Removed Errors Date",{"Supplier Name", "Date", "Reference", "180 Days", "150 Days", "120 Days", "90 Days", "60 Days", "30 Days", "Current", "Balance"})
in
    #"Reordered Columns Name"

Output looks like this:

20211226 Fill Creditor Name Down.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	Supplier Name	Date	Reference	180 Days	150 Days	120 Days	90 Days	60 Days	30 Days	Current	Balance
2	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	16/08/2021	BCI-MZN Transf				-1359.12				-1359.12
3	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	17/08/2021	BCI-MZN Transf				-33801.1				-35160.22
4	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	8/09/2021	BCI-MZN Transf					-1064.69			-36224.91
5	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	23/09/2021	BCI-MZN Transf					-2744.76			-38969.67
6	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	1/11/2021	2815							750	-38219.67
7	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	9/11/2021	BCI-MZN Transf							-78463	-116682.67
8	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	17/11/2021	2853							11726.88	-104955.79
9	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	23/11/2021	NED-MZN Transf							-30931.23	-135887.02
10	ABDU01 (Abdul Karim Osman - Despachnte Aduaneiro)	26/11/2021	2866							13940.02	-121947
11	ACPE01 (AC PECAS, Lda)	22/11/2021	CX-MZN 960							-819	-819
12	ADIL01 (Adilson da Silva)	30/11/2021	975							107640	107640
13	ADIL01 (Adilson da Silva)	30/11/2021	976							23400	131040
14	AFRI04 (Afri-Mechs Ltd)	12/01/2021	20	60117.42							60117.42
15	AFRI04 (Afri-Mechs Ltd)	30/04/2021	29	648226.8							708344.22
16	AFST01 (Advanced Fire Supression Technologies)	2/08/2021	INV02861			17828.46					17828.46
17	ALIE01 (Alie Investiment Lda)	18/09/2021	306					30.24			30.24
18	ALIE01 (Alie Investiment Lda)	4/10/2021	311						560000.1		560030.34
19	AMEL01 (Amelia Z.T.S Rendicao)	5/11/2021	3185							40	40
20	ANIF01 (Anif Comercial )	4/09/2021	2465					0.01			0.01
21	ASSO01 (Associacao Arteba)	22/09/2021	BU-MZN Transf					-12000			-12000
22	ASSO01 (Associacao Arteba)	2/10/2021	89						30000		18000
23	AUTO16 (Auto Sueco Mocambique (SA))	25/10/2021	A3/2021/7311103000						47809.63		47809.63
24	AUTO16 (Auto Sueco Mocambique (SA))	25/10/2021	A3/2021/7311103001						148778.67		196588.3
25	AUTO20 (Auto Stop Amirana, Lda)	6/07/2021	BU-MZN Transf			-228000					-228000
26	AUTO20 (Auto Stop Amirana, Lda)	2/08/2021	MOZA-MZN Transf			-228000					-456000
27	AVAN01 (Avani Pemba Beach Hotel & Spa)	8/11/2021	165145/2							3290	3290
28	AVAN01 (Avani Pemba Beach Hotel & Spa)	8/11/2021	165647/1							154879.96	158169.96
tblAgedCred

 

[Alex Blakenburg]

Oops I noticed by doing the PQ report that in my original post the formula in Column B "Data Row YN" should have been the below if you wanted to knock out the Totals rows as well.

=IF(OR(L2="",LEFT(C2,5)="Total"),"No","Yes")