Move Quickbooks data to Excel

[mrsphxbabe]

Hello, we use quickbooks for our accounting needs. The problem is that quickbooks (qbs) wont allow certain information to be displayed on some reports. We can pull a YTD sales report that shows a comparison from last year, but it will not display who the sales rep is for that company. Is there a formula or macro that can be used so that a column would be added automatically and could add the reps names in that column next to the correct company? Right now I have to run 35 separate reports, 1 report for each sales rep in order to get the sales for each individual rep. We want to run 1 report and have the reps automatically matched to the company name. Below is the example spread sheet, the column that has a header of "rep"is what needs to be added along with the sales rep. is this possible?

[TABLE="width: 1370"]
<colgroup><col><col><col><col><col span="2"><col><col><col span="2"><col><col><col span="2"></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]rep[/TD]
[TD]Jan 18[/TD]
[TD]Jan 17[/TD]
[TD]$ Change[/TD]
[TD]% Change[/TD]
[TD]Feb 18[/TD]
[TD]Feb 17[/TD]
[TD]$ Change[/TD]
[TD]% Change[/TD]
[TD]Mar 18[/TD]
[TD]Mar 17[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Aaron & Company - Flemington, NJ[/TD]
[TD]sa[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Ace Plumbing, Heating & Electrical[/TD]
[TD]sa[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1,160.00[/TD]
[TD="align: right"]-1,160.00[/TD]
[TD="align: right"]-100.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="colspan: 3"]AF SUPPLY[/TD]
[TD]as[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Brooklyn, NY - AF Supply[/TD]
[TD]asas[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Liberty - Hicksville NY - AF[/TD]
[TD]kijmnn[/TD]
[TD="align: right"]604.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]604.00[/TD]
[TD="align: right"]100.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]Brooklyn, NY - AF Supply - Other[/TD]
[TD]asd[/TD]
[TD="align: right"]44.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]44.00[/TD]
[TD="align: right"]100.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]954.00[/TD]
[TD="align: right"]88.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Total Brooklyn, NY - AF Supply[/TD]
[TD]adfaf[/TD]
[TD="align: right"]648.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]648.00[/TD]
[TD="align: right"]100.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]954.00[/TD]
[TD="align: right"]88.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]New York, NY - AF Supply[/TD]
[TD]adfaf[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]Quality - Harrison, NJ - AF Supply[/TD]
[TD]afda[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]844.00[/TD]
[TD="align: right"]-844.00[/TD]
[TD="align: right"]-100.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="colspan: 2"]AF SUPPLY - Other[/TD]
[TD] [/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Total AF SUPPLY[/TD]
[TD]afdaf[/TD]
[TD="align: right"]648.00[/TD]
[TD="align: right"]844.00[/TD]
[TD="align: right"]-196.00[/TD]
[TD="align: right"]-23.22%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]954.00[/TD]
[TD="align: right"]88.00[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Anew ******* & Bath Design[/TD]
[TD] [/TD]
[TD="align: right"]573.75[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]573.75[/TD]
[TD="align: right"]100.0%[/TD]
[TD="align: right"]78.75[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]78.75[/TD]
[TD="align: right"]100.0%[/TD]
[TD="align: right"]-157.50[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Atlantic Plumbing[/TD]
[TD]fafaf[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[/TR]
[TR]
[TD="colspan: 3"]Bathtique, LTD[/TD]
[TD] [/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.0%[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]283.60[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

Apart from contacting Quickbooks support and getting info from them, I'd suggest maybe creating a table of Companies with the reps name in the adjacent column, then using a VLOOKUP formula to source the reps name.

 

[mrsphxbabe]

Thank you Michael for the response. QuickBooks is no help. the reports are very limited. The VLOOKUP formula may be the easy solution, however its only easy if you know how to do it. I do not. any help would be greatly appreciated.
thanks again

 

[Michael M]

Create a table of data in say Sheet2 with the company names in column 1 and the associated reps name in col 2...like this

Excel 2007
	A	B
1	Aaron & Company - Flemington, NJ	bill
2	Ace Plumbing, Heating & Electrical	bill
3	AF SUPPLY	bill
4	Brooklyn, NY - AF Supply	fred
5	Liberty - Hicksville NY - AF	joe
6	Brooklyn, NY - AF Supply - Other	joe
7	Total Brooklyn, NY - AF Supply	joe
8	New York, NY - AF Supply	joe
9	Quality - Harrison, NJ - AF Supply	joe
10	AF SUPPLY - Other	joe
11	Total AF SUPPLY	joe
12	Anew ******* & Bath Design	joe
13	Atlantic Plumbing	joe
14	Bathtique, LTD	joe
Sheet2

then back in the report sheet, ASSUMING the reps are listed column 2....copy this formula and drag it down as required....

Excel 2007
	A	B	C	D	E
2	Aaron & Company - Flemington, NJ	bill	0	0	0
3	Ace Plumbing, Heating & Electrical	bill	0	1,160.00	-1,160.00
4	AF SUPPLY	bill
5	Brooklyn, NY - AF Supply	fred
6	Liberty - Hicksville NY - AF	joe	604	0	604
7	Brooklyn, NY - AF Supply - Other	fred	44	0	44
8	AF SUPPLY	bill	648	0	648
9	New York, NY - AF Supply	joe	0	0	0
10	Quality - Harrison, NJ - AF Supply	joe	0	844	-844
11	AF SUPPLY - Other	bill	0	0	0
12	Total AF SUPPLY	joe	648	844	-196
13	Anew ******* & Bath Design	bill	573.75	0	573.75
14	Atlantic Plumbing	bill	0	0	0
15	Bathtique, LTD	bill	0	0	0
Sheet1

Cell Formulas
Range	Formula
B2	=IFERROR(VLOOKUP(A2,Sheet2!$A$1:$B$14,2),"No Rep listed")

you will need to change both the references to the rows in the formulaes...see in red below

=IFERROR(VLOOKUP(A2,Sheet2![color=red]$A$1:$B$14[/color],2),"No Rep listed")