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

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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.
screenshot_excel.JPG


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!
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Saurabhj

Active Member
Joined
Jun 6, 2020
Messages
413
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
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

New Member
Joined
Feb 12, 2021
Messages
25
Office Version
  1. 365
Platform
  1. Windows
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
BCDEFGHIJK
18Account: 301 Wages and salaries
19
20DateDetailsReferenceTypeBatchDebitCreditBalance
21
2221/01/2019PURCHASE1Bank3665.00-665.00
2319/03/2019PURCHASE2Bank3120.00-785.00
2401/04/2019PURCHASE3Bank3384.00-1,169.00
2503/05/2019PURCHASE41Bank3500.00-1,669.00
2610/05/2019PURCHASE51Bank3500.00-2,169.00
2710/05/2019PURCHASE6Bank3500.00-2,669.00
2817/05/2019PURCHASE7Bank3500.00-3,169.00
2923/05/2019PURCHASE8Bank3500.00-3,669.00
3029/05/2019PURCHASE9Bank3585.00-4,254.00
3130/05/2019PURCHASE10Bank3500.00-4,754.00
3231/05/2019PURCHASE11Bank3375.00-5,129.00
3304/06/2019PURCHASE121Bank31,100.00-6,229.00
3405/06/2019PURCHASE13Bank3550.00-6,779.00
3506/06/2019PURCHASE14Bank3500.00-7,279.00
3607/06/2019PURCHASE15Bank3460.00-7,739.00
3707/06/2019PURCHASE16Bank3250.00-7,989.00
3813/06/2019PURCHASE17Bank3500.00-8,489.00
3914/06/2019PURCHASE18Bank3445.00-8,934.00
4014/06/2019PURCHASE19Bank3430.00-9,364.00
4117/06/2019PURCHASE201Bank3350.00-9,714.00
4218/06/2019PURCHASE211Bank3700.00-10,414.00
4320/06/2019PURCHASE22Bank3500.00-10,914.00
4425/06/2019PURCHASE231Bank3350.00-11,264.00
4527/06/2019PURCHASE24Bank3500.00-11,764.00
4628/06/2019PURCHASE25Bank390.00-11,854.00
4728/06/2019PURCHASE26Bank390.00-11,944.00
4802/07/2019PURCHASE271Bank3350.00-12,294.00
4904/07/2019PURCHASE28Bank3500.00-12,794.00
5010/07/2019PURCHASE291Bank3350.00-13,144.00
5111/07/2019PURCHASE30Bank3500.00-13,644.00
5215/07/2019PURCHASE31Bank3110.00-13,754.00
5315/07/2019PURCHASE32Bank3140.00-13,894.00
5418/07/2019PURCHASE33Bank3500.00-14,394.00
5522/07/2019PURCHASE341Bank3300.00-14,694.00
5625/07/2019PURCHASE35Bank3500.00-15,194.00
5726/07/2019PURCHASE36Bank3110.00-15,304.00
5826/07/2019PURCHASE37Bank3150.00-15,454.00
5901/08/2019PURCHASE38Bank3500.00-15,954.00
6002/08/2019PURCHASE39Bank3265.00-16,219.00
6102/08/2019PURCHASE40Bank3280.00-16,499.00
6208/08/2019PURCHASE41Bank3500.00-16,999.00
6315/08/2019PURCHASE42Bank3500.00-17,499.00
6416/08/2019PURCHASE43Bank3195.00-17,694.00
6521/08/2019PURCHASE441Bank3450.00-18,144.00
6622/08/2019PURCHASE45Bank3500.00-18,644.00
6729/08/2019PURCHASE46Bank3500.00-19,144.00
6830/08/2019PURCHASE47Bank380.00-19,224.00
6930/08/2019PURCHASE48Bank380.00-19,304.00
7005/09/2019PURCHASE49Bank3500.00-19,804.00
7112/09/2019PURCHASE50Bank3500.00-20,304.00
7219/09/2019PURCHASE51Bank3500.00-20,804.00
7318/10/2019PURCHASE521Bank3235.00-21,039.00
7423/10/2019PURCHASE531Bank3400.00-21,439.00
75
7621,439.00-21,439.00
77
78Account: 302 Directors remuneration
79
80DateDetailsReferenceTypeBatchDebitCreditBalance
81
8221/01/2019PURCHASE2Bank21,000.00-1,000.00
8318/02/2019PURCHASE3Bank2500.00-1,500.00
8401/03/2019PURCHASE4Bank21,000.00-2,500.00
8513/03/2019PURCHASE5Bank21,000.00-3,500.00
8618/04/2019PURCHASE6Bank21,000.00-4,500.00
8731/05/2019PURCHASE7Bank21,000.00-5,500.00
8819/06/2019PURCHASE8Bank21,000.00-6,500.00
8902/07/2019PURCHASE9Bank21,000.00-7,500.00
9026/07/2019PURCHASE10Bank21,000.00-8,500.00
9106/08/2019PURCHASE11Bank21,000.00-9,500.00
9203/09/2019PURCHASE12Bank21,000.00-10,500.00
9311/09/2019PURCHASE13Bank21,000.00-11,500.00
9403/10/2019PURCHASE14Bank21,000.00-12,500.00
9529/10/2019PURCHASE15Bank21,000.00-13,500.00
9619/11/2019PURCHASE16Bank21,000.00-14,500.00
9703/12/2019PURCHASE17Bank21,000.00-15,500.00
9811/12/2019PURCHASE18Bank21,000.00-16,500.00
99
10016,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
ABCDEFGHIJKLMNOP
112Account 301 Wages and salaries
113Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
11474433131/12/2019Example- AE21439.000.0021439.00JNLNN€0.00N
11577233631/12/2019Example26580.960.0028019.96JNLNN€0.00N
11677533731/12/2019Example319.640.0028039.60JNL381General expensesNN€0.00N
11728039.600.0028039.60
118
119Account 302 Directors remuneration
120Tran No.Bat No.DateRef No.NarrativeDebitCreditBalanceTypeAnalysisAnalysis DescriptionNBTaxCCQuantityTemporary
12174533131/12/2019ECExample4- AE16500.000.0016500.00JNLNN€0.00N
12277333631/12/2019ECExample59953.520.0026453.52JNLNN€0.00N
12326453.520.0026453.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!
 

Watch MrExcel Video

Forum statistics

Threads
1,128,206
Messages
5,629,290
Members
416,384
Latest member
frsamiee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top