Create column from data of another column / VBA automation

[may777]

Hi all,

So this is a report I'm exporting regularly, and what I do is create an extra column that states the "Partner Name".
(The software doesn't provide the option to just add a column of your choice)
I do this manually by creating a couple of helper columns and some IF formulas.
Also all headers must be moved one cell to the right cause they are wrong.
I'd love to have a macro to do this for me. I tried recording one but it doesn't work.
This is a trimmed down example. before and after.

Contract	Receipt	AE	Type	Characteristic	IssueDate	StartingDate	EndingDate	Client	Description	Money2	Money1	Money3	Money4	Money5
305191	1037397	1224	13	N	xyz	25/04/2023	02/05/2023	02/11/2023	ClientNameA		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Company Total	SomeCompanyName	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Branch Total	AT	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Partner Total	62	PartnerNameA	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
301000	1037352	1371	13	N	xyz	01/05/2023	[] 02/05/2023	[] 02/11/2023	ClientNameB		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
305157	1030438	1265	13	N	xyz	[] 27/04/2023	[] 02/05/2023	[] 02/11/2023	ClientNameC		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Company Total	SomeCompanyName	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Branch Total	AT	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Partner Total	63	PartnerNameB	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
307462	1042116		13	P	xyz	28/04/2023	[] 02/05/2023	[] 02/08/2023	ClientNameD		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Company Total	SomeCompanyName	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Branch Total	AT	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Partner Total	152	PartnerNameC	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
306611	2092174	9458	13	N	xyz	[] 01/05/2023	02/05/2023	[] 02/06/2023	ClientNameE		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
306612	2092177	9459	13	N	xyz	[] 01/05/2023	[] 02/05/2023	[] 02/08/2023	ClientNameF		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Company Total	SomeCompanyName	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Branch Total	ZS	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Partner Total	293	PartnerNameD	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Total	14	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
XYZ Software

AFTER
(So basically if I need to do a lookup from another excel, to find that contract number 1037397 belongs to PartnerNameA, now I can)

	Contract		Receipt	AE	Type	Characteristic	IssueDate	StartingDate	EndingDate	Client	Description	Money2	Money1	Money3	Money4	Money5
305191	1037397	PartnerNameA	1224	13	N	xyz	25/04/2023	02/05/2023	02/11/2023	ClientNameA		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Company Total	SomeCompanyName	PartnerNameA	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Branch Total	AT	PartnerNameA	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Partner Total	62	PartnerNameA	PartnerNameA	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
301000	1037352	PartnerNameB	1371	13	N	xyz	01/05/2023	[] 02/05/2023	[] 02/11/2023	ClientNameB		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
305157	1030438	PartnerNameB	1265	13	N	xyz	[] 27/04/2023	[] 02/05/2023	[] 02/11/2023	ClientNameC		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Company Total	SomeCompanyName	PartnerNameB	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Branch Total	AT	PartnerNameB	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Partner Total	63	PartnerNameB	PartnerNameB	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
307462	1042116	PartnerNameC		13	P	xyz	28/04/2023	[] 02/05/2023	[] 02/08/2023	ClientNameD		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Company Total	SomeCompanyName	PartnerNameC	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Branch Total	AT	PartnerNameC	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Partner Total	152	PartnerNameC	PartnerNameC	1	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
306611	2092174	PartnerNameD	9458	13	N	xyz	[] 01/05/2023	02/05/2023	[] 02/06/2023	ClientNameE		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
306612	2092177	PartnerNameD	9459	13	N	xyz	[] 01/05/2023	[] 02/05/2023	[] 02/08/2023	ClientNameF		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Company Total	SomeCompanyName	PartnerNameD	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Branch Total	ZS	PartnerNameD	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Partner Total	293	PartnerNameD	PartnerNameD	2	1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
Total	14		1,00 €	1,00 €	1,00 €	1,00 €	1,00 €
XYZ Software

Colors are only added to help get across what I'm asking.
Thanks in advance!

 

[Drrellik]

I think you used your Receipt Number and not your contract number in your description, but I think I understand what you are looking for generally the Partner(s) name is on the same row as the Partner Total

so if you find or look up 306611 in column A then the Partner name in Column C (before helper cells) is what you are looking for.

are you manually searching for this information or using another workbook in the same folder?

 

[may777]

I think you used your Receipt Number and not your contract number in your description, but I think I understand what you are looking for generally the Partner(s) name is on the same row as the Partner Total

so if you find or look up 306611 in column A then the Partner name in Column C (before helper cells) is what you are looking for.

are you manually searching for this information or using another workbook in the same folder?

No, like I said, the first table is the report that software exports. And it exports it with wrong headers. All headers must move one cell to the right.
That's what I've done to the 2nd table. (The numbers that you see in column A are some random ID of the software that I don't care about.)

In any case, the C column in the 2nd table is one I inserted and made manually, it's the end result I want. But I want to make it with VBA instead of manually.

 

[Drrellik]

Ok,

I was playing around with Index(Match and Offset but I see some of the Contract numbers are 4 rows apart and some are 5.

How are you "Searching" for the contract number when you open your workbook?

CTRL+F?

 

[may777]

Someone sends me an excel file (call it Excel-A) with some contract numbers plus some other info like issue date, client name. It doesn't contain any Partner information (but I need to know to which Partner every contract in Excel-A it belongs)

So then I export a report from our software (table1) that does have Partner information. But as you see in table1 it only mentions the Partner after listing all his contracts. Some partner may have 1 contract, someone else might have 100.
I manually get from table1 to table2 (this is what I'm asking help about, to make it with a macro).

Then I just do a vlookup from Excel-A to the the table2 to bring the partner information into Excel-A.

I'm not looking for formulas, I'm looking for a macro/vba code to turn table1 to table2.