How to match cell value and get other value

[Island Gryl]

Hi:

I have some excel basic excel abilities and have a need to go beyond them and not sure what the right approach is so am looking for guidance.

I have two worksheets that each have one common data field, "Order Name" (which is actually the Order number).

Sheet 1 Sales: is data based on a date range and includes all Order Names's for the period (potentially more than one occurrence if multiple items ordered, usually shipping) and the $ value of that Order Name, along with the tax on that order # if any.
Sheet 2 Taxes: is the same date range and includes the same Order Names and a Tax Label Field. Not every order has tax so those with no tax do not exist on this sheet.

My goal is to add the Tax Label Field from Sheet 1 to Sheet 2 and where the Order Number from Sheet 1 does not exist on sheet to Put Exempt.
This lets me report the correct amounts of Sales Revenue per Tax Type and remit the correct amounts of tax to the appropriate authorities

Is this a task for SUMIF or vlookup or ??

thank you in advance, much appreciate the help of the greater mind

Janice

 

[alansidman]

Attach a sample workbook (not a picture or pasted copy). Make sure there is just enough (we do not need many records) data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary. Use XL2BB to upload the sample data.

Remember to desensitize the data.

 

[Island Gryl]

Sheet 1

sales_2019-01-01_2019-05-06.csv
	A		C	D	E	F	G	H	I	J	K	L
1	order_name		orders	gross_sales	discounts	returns	net_sales	shipping	taxes	total_sales	net_quantity	Exempt Sales
2	#1002		0	0	0	0	0	0	0	0	0
3	#1002		1	39.99	-39.99	0	0	0	0	0	1
4	#1003		0	0	0	0	0	0	0	0	0
5	#1003		1	42.99	-4.29	0	38.7	0	0	38.7	1	Exempt
6	#1004		1	42.99	-8.6	0	34.39	0	1.72	36.11	1
7	#1004		1	39.99	-8	0	31.99	0	1.6	33.59	1
8	#1004		1	39.99	-7.99	0	32	0	1.6	33.6	1
9	#1004		1	39.99	-8	0	31.99	0	1.6	33.59	1
10	#1005		0	0	0	0	0	0	0	0	0
11	#1005		1	39.99	-7.99	0	32	0	3.84	35.84	1
12	#1006		0	0	0	0	0	0	0	0	0
13	#1006		1	37.99	0	0	37.99	0	4.56	42.55	1
14	#1006		1	37.99	0	0	37.99	0	4.56	42.55	1
15	#1006		1	37.99	0	0	37.99	0	4.56	42.55	1
16	#1007		1	35.99	-3.59	0	32.4	0	3.89	36.29	1
17	#1007		0	0	0	0	0	10	0	10	0
18	#1008		1	39.99	0	0	39.99	0	4.8	44.79	1
19	#1008		1	37.99	0	0	37.99	0	4.56	42.55	1
20	#1008		0	0	0	0	0	0	0	0	0
21	#1009		1	37.99	0	0	37.99	0	4.56	42.55	1
22	#1009		0	0	0	0	0	10	0	10	0
23	#1010		1	37.99	0	0	37.99	0	4.56	42.55	1
24	#1010		0	0	0	0	0	0	0	0	0
25	#1010		1	79.98	0	0	79.98	0	9.6	89.58	2
26	#1011		1	85.98	-10.37	0	75.61	0	9.08	84.69	2
27	#1011		1	39.99	-4.82	0	35.17	0	4.22	39.39	1
28	#1011		1	39.99	-4.81	0	35.18	0	4.22	39.4	1
29	#1012		0	0	0	0	0	7	0	7	0	Exempt
30	#1012		1	37	0	0	37	0	0	37	1	Exempt
sales_2019-01-01_2019-05-06

Sheet 2

taxes_2019-01-01_2019-05-06.csv
	B		D	E		G
1	order_name		tax_name	tax_rate		taxes
2	#1002		PST	0.07		0
3	#1002		GST	0.05		0
4	#1004		GST	0.05		1.72
5	#1004		GST	0.05		1.6
6	#1004		GST	0.05		1.6
7	#1004		GST	0.05		1.6
8	#1005		GST	0.05		1.6
9	#1005		PST	0.07		2.24
10	#1006		GST	0.05		1.9
11	#1006		PST	0.07		2.66
12	#1006		GST	0.05		1.9
13	#1006		PST	0.07		2.66
14	#1006		GST	0.05		1.9
15	#1006		PST	0.07		2.66
16	#1007		GST	0.05		1.62
17	#1007		PST	0.07		2.27
18	#1008		GST	0.05		1.9
19	#1008		GST	0.05		2
20	#1008		PST	0.07		2.66
21	#1008		PST	0.07		2.8
22	#1009		GST	0.05		1.9
23	#1009		PST	0.07		2.66
24	#1010		PST	0.07		5.6
25	#1010		GST	0.05		1.9
26	#1010		PST	0.07		2.66
27	#1010		GST	0.05		4
28	#1011		GST	0.05		1.76
29	#1011		PST	0.07		2.46
30	#1011		GST	0.05		3.78
31	#1011		PST	0.07		5.3
32	#1011		PST	0.07		2.46
33	#1011		GST	0.05		1.76
taxes_2019-01-01_2019-05-06

What I want to do is identify all the tax exempt sales and label them as such.
Sheet 1 has all order numbers (called order name) 1002 - 1012 for the example
Sheet 2 has the order number column but only the orders with Tax charged, exist here.
So my though is to compare sheet 1 to sheet 2 order numbers and when the order number doesn't exist on sheet 2, and the Total Sales is not zero, put Exempt

Once I have that I think I can generate the Sales & Tax figures I need from more simple SumIf's
The only "mock Up" I have done here is to write Exempt in the three fields that meet the criteria for this Order Batch.


Hopefully I did this correctly.
Thank you again for your guidance.

 

[Island Gryl]

I can't believe I have asked this stupid question and now can't delete it, embarrassed.

I was looking for a lot more complicated solution to this and now see the zero tax on the same sheet gives me the same answer as comparing the two.
Please ignore this request

 

[Fluff]

Hi &welcome to MrExcel
How about
=IF(AND(J2>0,COUNTIFS('taxes_2019-01-01_2019-05-06'!A:A,A2)=0),"Exempt","")

 

[Island Gryl]

Looking at this some more, the requested solution is actually better as just using the Zero Tax field picks up returns and non-taxable shipping. It is just perhaps not necessary ,
Thank you for your suggestion, I will try it this weekend, have to go to the day job now!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Island Gryl]

HI There,
I was able to recreate the formula, monkey see monkey do
But it starts to give me Exempt at row 6, 7 & 8 etc. which is wrong.

I entered the formula and drew it down the page allowing it to renumber as it went so if this section COUNTIFS('Taxes '!A:A,A2)=0),"Exempt","") looks at the order number (Taxes & Sales) this won't work because the orders with no tax don't exist on sheet, Taxes. #1003 for example.

This is how the whole formula for line 6 is on my sheet
=IF(AND(J6>0,COUNTIFS('Taxes '!A:A,A6)=0),"Exempt","")
It is returning Exempt when it's has tax

This is the data in Sales Line 6

#1004

1​

42.99​

-8.6​

0​

34.39​

0​

1.72​

36.11​

1​

Exempt

This is the date for Taxes Line 6, it is the same order only because order #1004 has multiple lines, it could easily be a different order number

2019-03-01​

#1004

GST

0.05​

Canada

1.6​

Since Order Number 1003 doesn't exist on the Taxes sheet and order numbers take a random number of lines, this approach not doable I think?

New approach
I'm thinking a simpler approach, =Sumif (Column G ( which does not include shipping) and test for zero in the Taxes Column I
This can all be done within the one sheet.

=SUMIF(I2:I184,"=0",G2:G184)

Got this to work, but now as a last fine tune, would like to add another filter for eliminating any order with "Shipping" in Column B.
the full description is "Shipping Fee" hopefully that is not an issue, but as of now I am looking to Sum column G if Column I is = 0 and column B does not contain "Shipping".

Janice

 

[Island Gryl]

Got it!

=SUMIFS(G2:G184, I2:I184,"=0", B2:B184,"<>Shipping Fee")

Took all day, but I learned a lot