extract data from sheet2 into sheet1

[Just2learn]

I have a workbook with two sheets. Sheet1 is simple it has a list of customers and a total column. Sheet2 has all the customers data. I need to pull the total by customer from sheet2 into sheet1. The problem I am having is the customer total cell on sheet2 is not always in the same place. i.e. the data may be on one line as in A ONE FURNITURE, or more than one as in the STARSHINE BEST line.

A ONE FURNITURE LLC 332.79
CUSTOMER TOTAL: 332.79

STARSHINE BEST LLC AR2198-0209 72129 619.94
STARSHINE BEST LLC FREIGHT CHARGE/P 721289 0
STARSHINE BEST LLC GA1803-0214 72240 1805.64
STARSHINE BEST LLC FREIGHT CHARGE/D 72239L 0
STARSHINE BEST LLC MI2203-0307 72486 328
STARSHINE BEST LLC FREIGHT CHARGE/P 724850 0
CUSTOMER TOTAL: 2753.58

BACKYARD PLEASURES 3963 72330 639
BACKYARD PLEASURES 3963 72331 8434.48
BACKYARD PLEASURES FREIGHT CHARGE/P72329 0
BACKYARD PLEASURES 3963 72420 3592.34
CUSTOMER TOTAL: 12665.82

I need to pull these customer totals into sheet1 that matches the customers name. There may be as many 900 customer names on sheet1. Any help would be greatly appreciated.....

 

[Haseeb Avarakkan]

Hello,

In Sheet2 are all data in one col. say A:A? Or customers in Col A & Totals in Col B?

 

[Just2learn]

Hi, the customers are listed in column B, and the totals are in column F. However in column F there may be numerous dollar figures each in a different row and a grand total at the bottom. Is there an easy way to post a snapshot of sheet2 in this thread?

Steve

 

[Haseeb Avarakkan]

Assume in Sheet2 B:B is the customer & down 'CUSTOMER TOTAL' & col F is the amounts.

Sheet1 A2 to down is the customer names, B2 copy down

=INDEX(INDEX(Sheet2!F:F,MATCH(A2&"*",Sheet2!B:B,0)):Sheet2!$F$65536,MATCH("Customer Total*",INDEX(Sheet2!B:B,MATCH(A2&"*",Sheet2!B:B,0)):Sheet2!$B$65536,0))

See my signature block. It will tell you how to post a snapshot of your sheet.

 

[Just2learn]

WOW this looks like a lot. I will give this a try. I am impressed how quickly you threw this together. thanks for the info and I will post my results and if I can't get it to work I will post sheet2 for you to see. Thanks...

 

[Just2learn]

I keep getting the #N/A error. Posted is sheet2, I hope i did it correctly. It starts with column A. Please let me know if theres anything i can do to assist you in helping me. Thanks alot...

Steve

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Page:	1	SUMMARY SALES ANALY	SIS			Report Ge	nerated On: 0	5/20/2001	1
2	========	==================================	===================	======	===	=========	=============	=============	======	===
3	A ONE	A ONE FURNITURE LLC	231	72219		332.79	0	332.79	0	%
4			CUSTOMER	TOTAL:		332.79	0	332.79	0	%
5
6	STABES	STARSHINE BEST LLC	AR2198-0209	72129		619.94	0	619.94	0	%
7	STABES	STARSHINE BEST LLC	FREIGHT CHARGE/P	721289		0	0	0	0	%
8	STABES	STARSHINE BEST LLC	GA1803-0214	72240		1805.64	0	1805.64	0	%
9	STABES	STARSHINE BEST LLC	FREIGHT CHARGE/D	72239L		0	0	0	0	%
10	STABES	STARSHINE BEST LLC	MI2203-0307	72486		328	0	328	0	%
11	STABES	STARSHINE BEST LLC	FREIGHT CHARGE/P	724850		0	0	0	0	%
12			CUSTOMER	TOTAL:		2753.58	0	2753.58	0	%
13	BACPLE	BACKYARD PLEASURES	3963	72330		639	0	639	0	%
14	BACPLE	BACKYARD PLEASURES	3963	72331		8434.48	31.6	8402.88	99.63	%
15	BACPLE	BACKYARD PLEASURES	FREIGHT CHARGE/P	72329		0	0	0	0	%
16	BACPLE	BACKYARD PLEASURES	3963	72420		3592.34	0	3592.34	0	%
17			CUSTOMER	TOTAL:		12665.82	31.6	12634.22	99.75	%
18
19	PATBON	PATIO BONANZA	0207-252	72599		27.5	203.25	-175.75	0	%
20			CUSTOMER	TOTAL:		27.5	203.25	-175.75	0	%
21
22			GRAND TOTA	L ALL:		556863.46	30349.13	526514.33	94.55	%
Sheet2

Excel 2007

 

[Just2learn]

This is sheet1 layout....

Excel Workbook
	A	B
1	Name	total
2	A ONE FURNITURE LLC
3	A ONE FURNITURE LLC
4	A&A DISTRIBUTORS
5	AARON FEBER
6	ABRAHAM GEORGE INC
7	ABRAHAM GEORGE INC
8	***do not use***
9	ALLSTAR POOL AND SPA #2151
10	ALONG CAME MARY
11	BACKYARD PLEASURES
12	AMERICAN POOL #215253
13	PATIO BONANZA
Sheet1

Excel 2007

 

[Just2learn]

Haseeb, I did get results, I did not notice i had customer and total in two different cells, when I moved "customer total to column B it pulled the data. Does "customer total have to stay in column B or can that be in a different column? Thank you for such a quick reply and a solution that extracted the data. Nice job...

Steve

 

[Haseeb Avarakkan]

B2, copy down.

=IFERROR(INDEX(INDEX(Sheet2!F:F,MATCH(A2&"*",Sheet2!B:B,0)):Sheet2!$F$65536,MATCH("Total*",INDEX(Sheet2!D:D,MATCH(A2&"*",Sheet2!B:B,0)):Sheet2!$D$65536,0)),0)

Also,

A simple SUMIF would do it.

=SUMIF(Sheet2!B:B,$A2,Sheet2!F:F)

 

[Just2learn]

Sir it works like a charm and I did not have to modify my sheets at all...Thank you ever so much for your help.

Steve