Search for a value in a column, then finding specific values under the first value and coping the data to another workbook of similar headings

[Maccers93]

Hi guys,

I am very new to this and I would much appreciate any assistance.

I have two workbooks from two different software. Both Workbooks have similar headings but we want to replace one line in Workbook2 with more than one line in Workbook1.

There are multiple headings, and I am wondering if:
- how can multiple values be read in column A?
- how can certain headings be read under this value?
- how to copy data under those headings to the same headings of a different workbook and replace one row with multiple rows?

In the image it may be clearer. I want to read the Account: 302 in Workbook1. I want to then read the Date, Details, Reference in the row below this heading. and copy the data below these headings.
When pasting into the Workbook2 I want to read Account 302 if in Workbook1 is Account: 302, and paste the data copied under the same headings in the second workbook replacing one row with multiple data from Workbook1.

This must happen with more than one value also but if I can get this one I can try and figure out how to read all the other values.

Thanks in advance guys, I hope I was clear with my explanation!

 

[Saurabhj]

Hi, Welcome to MrExcel board.

Please share data of both workbooks using XL2BB. It would help to copy the data and understand the requirement.

 

[Maccers93]

Hi, Welcome to MrExcel board.

Please share data of both workbooks using XL2BB. It would help to copy the data and understand the requirement.

Thank you for your reply, I do appreciate it @Saurabhj!

Workbook1.xlsx
	B	C	D	E	F	G	H	I	J	K
18	Account: 301 Wages and salaries
19
20	Date	Details	Reference	Type	Batch	Debit		Credit		Balance
21
22	21/01/2019	PURCHASE1		Bank	3	665.00		-		665.00
23	19/03/2019	PURCHASE2		Bank	3	120.00		-		785.00
24	01/04/2019	PURCHASE3		Bank	3	384.00		-		1,169.00
25	03/05/2019	PURCHASE4	1	Bank	3	500.00		-		1,669.00
26	10/05/2019	PURCHASE5	1	Bank	3	500.00		-		2,169.00
27	10/05/2019	PURCHASE6		Bank	3	500.00		-		2,669.00
28	17/05/2019	PURCHASE7		Bank	3	500.00		-		3,169.00
29	23/05/2019	PURCHASE8		Bank	3	500.00		-		3,669.00
30	29/05/2019	PURCHASE9		Bank	3	585.00		-		4,254.00
31	30/05/2019	PURCHASE10		Bank	3	500.00		-		4,754.00
32	31/05/2019	PURCHASE11		Bank	3	375.00		-		5,129.00
33	04/06/2019	PURCHASE12	1	Bank	3	1,100.00		-		6,229.00
34	05/06/2019	PURCHASE13		Bank	3	550.00		-		6,779.00
35	06/06/2019	PURCHASE14		Bank	3	500.00		-		7,279.00
36	07/06/2019	PURCHASE15		Bank	3	460.00		-		7,739.00
37	07/06/2019	PURCHASE16		Bank	3	250.00		-		7,989.00
38	13/06/2019	PURCHASE17		Bank	3	500.00		-		8,489.00
39	14/06/2019	PURCHASE18		Bank	3	445.00		-		8,934.00
40	14/06/2019	PURCHASE19		Bank	3	430.00		-		9,364.00
41	17/06/2019	PURCHASE20	1	Bank	3	350.00		-		9,714.00
42	18/06/2019	PURCHASE21	1	Bank	3	700.00		-		10,414.00
43	20/06/2019	PURCHASE22		Bank	3	500.00		-		10,914.00
44	25/06/2019	PURCHASE23	1	Bank	3	350.00		-		11,264.00
45	27/06/2019	PURCHASE24		Bank	3	500.00		-		11,764.00
46	28/06/2019	PURCHASE25		Bank	3	90.00		-		11,854.00
47	28/06/2019	PURCHASE26		Bank	3	90.00		-		11,944.00
48	02/07/2019	PURCHASE27	1	Bank	3	350.00		-		12,294.00
49	04/07/2019	PURCHASE28		Bank	3	500.00		-		12,794.00
50	10/07/2019	PURCHASE29	1	Bank	3	350.00		-		13,144.00
51	11/07/2019	PURCHASE30		Bank	3	500.00		-		13,644.00
52	15/07/2019	PURCHASE31		Bank	3	110.00		-		13,754.00
53	15/07/2019	PURCHASE32		Bank	3	140.00		-		13,894.00
54	18/07/2019	PURCHASE33		Bank	3	500.00		-		14,394.00
55	22/07/2019	PURCHASE34	1	Bank	3	300.00		-		14,694.00
56	25/07/2019	PURCHASE35		Bank	3	500.00		-		15,194.00
57	26/07/2019	PURCHASE36		Bank	3	110.00		-		15,304.00
58	26/07/2019	PURCHASE37		Bank	3	150.00		-		15,454.00
59	01/08/2019	PURCHASE38		Bank	3	500.00		-		15,954.00
60	02/08/2019	PURCHASE39		Bank	3	265.00		-		16,219.00
61	02/08/2019	PURCHASE40		Bank	3	280.00		-		16,499.00
62	08/08/2019	PURCHASE41		Bank	3	500.00		-		16,999.00
63	15/08/2019	PURCHASE42		Bank	3	500.00		-		17,499.00
64	16/08/2019	PURCHASE43		Bank	3	195.00		-		17,694.00
65	21/08/2019	PURCHASE44	1	Bank	3	450.00		-		18,144.00
66	22/08/2019	PURCHASE45		Bank	3	500.00		-		18,644.00
67	29/08/2019	PURCHASE46		Bank	3	500.00		-		19,144.00
68	30/08/2019	PURCHASE47		Bank	3	80.00		-		19,224.00
69	30/08/2019	PURCHASE48		Bank	3	80.00		-		19,304.00
70	05/09/2019	PURCHASE49		Bank	3	500.00		-		19,804.00
71	12/09/2019	PURCHASE50		Bank	3	500.00		-		20,304.00
72	19/09/2019	PURCHASE51		Bank	3	500.00		-		20,804.00
73	18/10/2019	PURCHASE52	1	Bank	3	235.00		-		21,039.00
74	23/10/2019	PURCHASE53	1	Bank	3	400.00		-		21,439.00
75
76						21,439.00		-		21,439.00
77
78	Account: 302 Directors remuneration
79
80	Date	Details	Reference	Type	Batch	Debit		Credit		Balance
81
82	21/01/2019	PURCHASE2		Bank	2	1,000.00		-		1,000.00
83	18/02/2019	PURCHASE3		Bank	2	500.00		-		1,500.00
84	01/03/2019	PURCHASE4		Bank	2	1,000.00		-		2,500.00
85	13/03/2019	PURCHASE5		Bank	2	1,000.00		-		3,500.00
86	18/04/2019	PURCHASE6		Bank	2	1,000.00		-		4,500.00
87	31/05/2019	PURCHASE7		Bank	2	1,000.00		-		5,500.00
88	19/06/2019	PURCHASE8		Bank	2	1,000.00		-		6,500.00
89	02/07/2019	PURCHASE9		Bank	2	1,000.00		-		7,500.00
90	26/07/2019	PURCHASE10		Bank	2	1,000.00		-		8,500.00
91	06/08/2019	PURCHASE11		Bank	2	1,000.00		-		9,500.00
92	03/09/2019	PURCHASE12		Bank	2	1,000.00		-		10,500.00
93	11/09/2019	PURCHASE13		Bank	2	1,000.00		-		11,500.00
94	03/10/2019	PURCHASE14		Bank	2	1,000.00		-		12,500.00
95	29/10/2019	PURCHASE15		Bank	2	1,000.00		-		13,500.00
96	19/11/2019	PURCHASE16		Bank	2	1,000.00		-		14,500.00
97	03/12/2019	PURCHASE17		Bank	2	1,000.00		-		15,500.00
98	11/12/2019	PURCHASE18		Bank	2	1,000.00		-		16,500.00
99
100						16,500.00		-		16,500.00
AccountDetails

Above is Workbook1, I need to copy data from Account: 301 and Account: 302. The data would be under all the subheadings (Date, Debit, Credit, etc.)

Workbook2.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
112	Account 301 Wages and salaries
113	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
114	744	331	31/12/2019		Example- AE	21439.00	0.00	21439.00	JNL			N	N		€0.00	N
115	772	336	31/12/2019		Example2	6580.96	0.00	28019.96	JNL			N	N		€0.00	N
116	775	337	31/12/2019		Example3	19.64	0.00	28039.60	JNL	381	General expenses	N	N		€0.00	N
117						28039.60	0.00	28039.60
118
119	Account 302 Directors remuneration
120	Tran No.	Bat No.	Date	Ref No.	Narrative	Debit	Credit	Balance	Type	Analysis	Analysis Description	NB	Tax	CC	Quantity	Temporary
121	745	331	31/12/2019	EC	Example4- AE	16500.00	0.00	16500.00	JNL			N	N		€0.00	N
122	773	336	31/12/2019	EC	Example5	9953.52	0.00	26453.52	JNL			N	N		€0.00	N
123						26453.52	0.00	26453.52
Sheet1

And the data pasted from Workbook1, would need to replace the line under Account 301 and Account 302 in Workbook2 with the narrative containing - AE and under the same subheadings.

Please let me know if this is possible as I have been researching and I do not know the best approach for this.

Thanks again in advance for any contributions or solutions!