Need Assistance with either a Formula or VBA.

[Giovanni03]

Hi!

I've been working on a report and I'm currently stuck on a section in which I'm trying to pull the qty order numbers are listed from one sheet and paste that qty on another. I've been trying to use formulas to make this work but i cant seem to get it done, I've used countifs, sum, and combination of both.

On sheet "Report" column H its where I want to have the qty of orders listed. On sheet "Drain" column J is where all the orders are listed. The crucial parts is that column B on sheet "Report" must match column D on sheet "Drain". Also need to exclude counting duplicates.

Below is an example

Column J

7419638

4270805

7434994

7434994

9114133

9114133

9114133

All of the orders above are assigned to "RT004" which both sheets list. so the RT004 has 4 orders.

Appreciate any help with this!!!

 

[Joe4]

That sample isn't very robust. Showing just one column doesn't quite give us all the needed information.
Can you post and the necessary columns from BOTH sheets, so we can see exactly what all your data looks like?

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[Giovanni03]

I apologize for the really late reply, i've been caught up with a lot of projects.

Below is an example of what im trying to find a solution to. On sheet Report im looking to find the total of orders that were incomplete by the driver. So in column E the driver had 15 stops. Sheet Drain shows how many orders were incomplete (Column J), in this case it was 4. But one of the issues is that order numbers are duplicated due to them having more than one item on it.

So im looking to have excel identify how many orders were incomplete and paste it into sheet report column H by driver. So column C on sheet Report must match Column D on sheet Drain.

Book1
	A	B	C	D	E	F	G	H
1	Driver	Trailer Number	Location	Routed Stops	Actual Stops	Qty R/E Orders	Incompleted R/E Orders	Incomplete Orders
2	John Doe # 1	001C_073123	RTSTG004A	18	15	0	0
Report

Book1
	A	B	C	D	E	F	G	H	I	J
1	status	RouteId	ReasonCode	stagingLocation	carrierMove	wave	shipmentID	stop	openBoxFlag	orderNumber
2	Good	2319_001C		RTSTG004A	480775	670573	865819	11		252100
3	Good	2319_001C		RTSTG004A	480775	670573	865807	15		337022
4	Good	2319_001C		RTSTG004A	480775	670573	865809	17		419638
5	Good	2319_001C		RTSTG004A	480775	670573	865809	17		419638
6	Good	2319_001C		RTSTG004A	480775	670573	865811	18		270805
7	Good	2319_001C		RTSTG004A	480775	670573	865811	18		270805
Drain

 

[Joe4]

On your "Drain" sheet, what field is used to match those record to the particular driver on your "Report" sheet?
I imagine that your "Drain" sheet will probably have lots of data for different drivers. If that is the case, we need the ability to match up the two lists by driver.
Or are we matching on "Location/stagingLocation" (the only common field I can see between the two lists).

 

[Joe4]

If the above assumption is correct, and we are matching on Location, putting this formula in cell H2 on your Report sheet will return your desired value of 4:

=SUM(--(LEN(UNIQUE(FILTER(Drain!J$2:J$7,Drain!D$2:D$7=C2,"")))>0))

For an explanation and other methods, see: Count Unique Text Values with Criteria in Excel (5 Methods) - ExcelDemy

 

[Giovanni03]

If the above assumption is correct, and we are matching on Location, putting this formula in cell H2 on your Report sheet will return your desired value of 4:

=SUM(--(LEN(UNIQUE(FILTER(Drain!J$2:J$7,Drain!D$2:D$7=C2,"")))>0))

For an explanation and other methods, see: Count Unique Text Values with Criteria in Excel (5 Methods) - ExcelDemy

Thank you so much!!! that worked perfectly!!

 

[Joe4]

You are welcome.
Glad I was able to help!