Transfer data from Sheet2 to Sheet1

Panoos64 · Mar 29, 2015

Hi all, i would like to use the function VLOOKUP or VBA code so that to compare two criteria. Where in Sheet1 find the same name company in Sheet2 should be place the amount from col. "C" (Sheet2) in col."B" at Sheet1 if the first letters before number in col. "B" is"CRN" as my below example. In Sheet1 are the data and in Sheet2 is the expected result. The spreadsheet contains more than 60000 rows. Appreciate any help in order to solve the above project. Thanking you in advance

Sheet2

	A	B	C	D
1	Date	Reference	Debit	Credit
2	ABCD LTD
3	8/7/2014	CRN-08071405	-2950	0
4	30/12/2014	ADJ-30121403	0	550
5	31/12/2014	REC-31121408	0	8255
6	EFGH LTD
7	25/8/2014	CRN-25081407	-3780	0
8	30/12/2014	REC-30121410	0	4250
9	31/12/2014	ADJ-31121405	0	485

Sheet1

	A	B	C	D
1	Company	Credit Notes	Receipts	Adjustments
2	ABCD LTD	2950
3	EFGH LTD	3780

vllgv · Mar 29, 2015

Hi,

I am making the assumption that under each Company on Sheet2 there are 3 rows and 4 columns.

I entered this formula on Sheet1 B2: =VLOOKUP("CRN*",OFFSET(INDIRECT("Sheet4!"&ADDRESS(MATCH(Sheet3!A2,Sheet4!A1:A9,0),1)),1,1,3,3),2,FALSE) and copied down.

Panoos64 · Mar 29, 2015

Thank you vllgv for your support. I think there is something wrong. When i entered the above formula in Sheet1 cell B2 and copied down, it appears #N/A. Maybe the wrong is that i work with Sheet2 and Sheet1 and in formula you entered Sheet4 and Sheet3. However i tried to change the Sheet numbers in formula but unfortunately it appears the same error. Appreciate to help me to resolve my project. Thanks once again.

vllgv · Mar 29, 2015

Not sure. I would have to see a copy of the workbook.

Panoos64 · Mar 30, 2015

I am apologizing vllgv for my first notice. I had done a mistake when i was writing the formula. It works perfect. Thanks once again for your support and i wish you all the best. Regards

Mike Szczesny · Mar 31, 2015

Can you use something like this?

Excel 2012

A

B

C

D

1

Date

Reference

Debit

Credit

2

ABCD

3

08/07/14

CRN-1000

-2950.00

1000.00

4

08/14/14

ADJ-500

550.00

5

08/17/14

REC-7500

1000.00

6

DEFG

7

9/15/2014

CRN-2000

50005.00

8

9/18/2014

ADJ-450

4250.00

9

9/27/2014

REC-6100

485.00

10

11

Company

CRN

Receipts

Adjustments

12

ABCD

-1950.00

13

DEFG

50005.00

Sheet1

Cell Formulas
Range	Formula
A12	{=IFERROR(INDEX($A$2:$A$9,SMALL(IF(ISTEXT($A$2:$A$9),ROW($A$2:$A$9)-ROW($A$2)+1),ROWS($A$12:A12))),"")}
B12	{=INDEX($C$3:$C$9+$D$3:$D$9,SMALL(IF(ISNUMBER(SEARCH($B$11,$B$3:$B$9)),ROW($C$3:$C$9)-ROW($C$3)+1),ROWS($B$12:B12)))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Panoos64 · Mar 31, 2015

Thank you Mike for your attempt to help me to resolve my project. The formulas are working but is a little bit complicated due to the use of 2 formulas. Furthermore i use 2 Sheets. In addition i have problem because the data are mess. e.g. There are debtors names with blank rows (no data) and some of the them have just in one or two rows data as the below extract. If anyone can help me to adjust the formula according to the below extract would be greatly appreciated. Thanking you in advance.

Sheet2

	A	B	C	D
1	Date	Reference	Debit	Credit
2	ABCD LTD
3	8/7/2014	CRN-08071405	-2950	0
4	30/12/2014	ADJ-30121403	0	550
5	31/12/2014	REC-31121408	0	8255
6	EFGH LTD
7	25/8/2014	CRN-25081407	-3780	0
8	31/12/2014	REC-31121410	0	4250
9	30/12/2014	ADJ-30121405	0	485
10	HIJK LTD
11	LMNO LTD
12	7/1/2014	REC-07011402		2500
13	15/3/2014	ADJ-15031405		55
14	PQRS LTD
15	15/4/2014	CRN-15041409	-1555

Sheet1

	A	B	C	D
1	Company	Credit Notes	Receipts	Adjustments
2	ABCD LTD	2950
3	EFGH LTD	3780
4	PQRS LTD	1555

Mike Szczesny · Mar 31, 2015

Those formulas should still work. You may have to do some tweaking to the formula. The first formula captures all text, including the company names with no data. Using another worksheet should not pose a problem. Using a mouse your formula will include notation such as Sheet1!data_range.... etc. For those companies who did not have data. If you can modify your source data file, you can possibly set up your CRN-???? account fields for the companies who do not have data. The second formula will show will show blank or 0.00 (not sure which). Also the formula assumes you would have credits in your CRN data. If no credits, then you can just then can remove the D range from the index part of the formula. Hope this helps.

jim may · Mar 31, 2015

Just playing around with your WS - note Subset of output in Columns F,G,H & I; Then applying filter to Only F:I in the Second Screen Shot Below...

Excel 2012

	A	B	C	D	F	G	H	I
1	Date	Reference	Debit	Credit	Company	Credit Notes	Receipts	Adjustments
2	ABCD LTD				ABCD LTD	2950
3	41858	CRN-08071405	-2950	0
4	30/12/2014	ADJ-30121403	0	550
5	31/12/2014	REC-31121408	0	8255
6	EFGH LTD				EFGH LTD	3780
7	25/8/2014	CRN-25081407	-3780	0
8	31/12/2014	REC-31121410	0	4250
9	30/12/2014	ADJ-30121405	0	485
10	HIJK LTD
11	LMNO LTD
12	41821	REC-07011402		2500
13	15/3/2014	ADJ-15031405		55
14	PQRS LTD				PQRS LTD	1555
15	15/4/2014	CRN-15041409	-1555

Sheet1

Worksheet Formulas

Cell	Formula
F2	=IF(C3<0,OFFSET(C3,-1,-2),"")
G2	=IF(C3<>0,C3*-1,"")

Excel 2012

	A	B	C	D	F	G	H	I
1	Date	Reference	Debit	Credit	Company	Credit Notes	Receipts	Adjustments
2	ABCD LTD				ABCD LTD	2950
6	EFGH LTD				EFGH LTD	3780
14	PQRS LTD				PQRS LTD	1555

Sheet1

Panoos64 · Apr 1, 2015

Thank you jim! It is a simple way to resolve my project which was seems to me very complicated and i was searching at least a month ago. According to the above data i would like to complete the table making the same for "Receipts" and '"Adjustments" as the below extract which is the expected result. I considered that it was easy to do it, using copy but is not. Thanking you in advance.

Sheet1

	F	G	H	I
1	Company	Credit Notes	Receipts	Adjustments
2	ABCD LTD	2950	8255	550
3	EFGH LTD	3780	4250	485
4	LMNO LTD		2500	55
5	PQRS LTD	1555

Transfer data from Sheet2 to Sheet1

Panoos64

Well-known Member

Excel Facts

vllgv

Board Regular

Panoos64

Well-known Member

vllgv

Board Regular

Panoos64

Well-known Member

Mike Szczesny

Active Member

Panoos64

Well-known Member

Mike Szczesny

Active Member

jim may

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Transfer data from Sheet2 to Sheet1

Well-known Member

Excel Facts

Board Regular

Well-known Member

Board Regular

Well-known Member

Active Member

Well-known Member

Active Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock