HELP - Extract data from table (confusing)

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
317
Hope i can explain this properly. I've managed to find a way of getting the data (sort of) but when i go back over it i'm finding it hard to link the columns of A (branch), L (Primary Branch), M (Secondary Branch).

Below first table is a snapshot of weekly transport movements. Column A shows the branch who actually did the delivery. Column K shows the postcode this branch delivered to. We've allocated branches certain postcodes and i have a lookup that looks at "Del Postcode" and populates "Primary" and Secondary" automatically.

Now my problem is, in the second table i need to show results from the data. We've agreed branch 168 can deliver to certain postcodes allocated to branch 205, 205 has it's own postcodes so these need to be counted in the same manner. Not all postcodes have a secondary branch.

If it's easier i can send the workbook.



BranchCustomer NameAccount CodeOrder No.Order ValueDate CreatedDelivery DateAddress 1Delivery TypeVehicle RegDel PostcodePrimary BranchSecondary Branch
168​
TestTestTestTestTestTestTestDeliveryDG64VUVG42
206​
205​
168​
TestTestTestTestTestTestTestDeliveryDG64VUVG72
168​
205​
168​
TestTestTestTestTestTestTestDeliveryDK66JZWPA7
3988​
168​
TestTestTestTestTestTestTestDeliveryDK66JZWEH53
168​
205​
168​
TestTestTestTestTestTestTestDeliveryDG64VUVKA21
205​
168​
TestTestTestTestTestTestTestDeliveryDG64VUVKA21
205​



Branch NumberTotal DeliveriesCompliantNon-CompliantBranch Performance
168 & 205
205
206
371
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
941
Office Version
  1. 365
Platform
  1. Windows
Can you share your expected result from the above data.
 

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
317
Can you share your expected result from the above data.
Probably wasn't a good selection of data. The actual table is approx 1000 rows. Below has been populated based on the info mentioned in my 1st thread.

Column A counts the number of deliveries a branch has done. So if branch 168 is matched in L or M it's compliant. As row 3's postcode is assigned to branch 3988 it's not compliant. I've also added below the full results table which shows which branches are assigned to primary and secondary branches. You'll notice branch 849 is a secondary branch for two other branches which is why the main lookup table looks at the postcode then attached the branch so providing column A is checking against L & M there won't be any miscounts. I just can't think how to do it :(

Branch NumberTotal DeliveriesCompliantNon-CompliantBranch Performance
168651
205000
206000
371000


Branch NumberTotal DeliveriesCompliantNon-CompliantBranch Performance
168 *****************
205 ****************
206
371
442 !!!!!!!!!!!!!!!!!!!!!
983 !!!!!!!!!!!!!!!!!!!!!
700 """""""""""""""
849 """""""""""""""
738
763 ---------------
849 ---------------
925
3107
3109 ++++++++
442 +++++++++
3114
3514
3630 ========
700 ========
3653
3825
3857
3858
3988
4010
4071 ^^^^^^^^
669 ^^^^^^^^
4335
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
941
Office Version
  1. 365
Platform
  1. Windows
I have partially understood your requirement
Let me reiterate what i understood;

You want to count total complaint and Non Compliant

Complaint Means
As in row 2, the delivery is made to primary branch 168 and secondary branch mentioned is 205, is in this way 168 and 205 is linked.???
 

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
317

ADVERTISEMENT

I have partially understood your requirement
Let me reiterate what i understood;

You want to count total complaint and Non Compliant

Complaint Means
As in row 2, the delivery is made to primary branch 168 and secondary branch mentioned is 205, is in this way 168 and 205 is linked.???
Sorry it's all confusing. So, we have a main table which lists every postcode in the country. We've then assigned every a branch to every postcode. Because some postcodes are between branches we've assigned primary and secondary postcodes.

From this we extract info from our system and populate Excel. Column A shows the branch that did the job. Column L & M show the branches that column K (postcode) is linked to. I need to count the number of deliveries per branch then count how many are compliant (they are only compliant if column A matches what's in column L or M for that line).
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
941
Office Version
  1. 365
Platform
  1. Windows
For Branch 168 how did you get complaint as 5 and Non Complaint as 1

they are only compliant if column A matches what's in column L or M for that line).
How does 168 match with row a, row e and row f
 

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
317

ADVERTISEMENT

For Branch 168 how did you get complaint as 5 and Non Complaint as 1


How does 168 match with row a, row e and row f
Sorry, that was meant to be 4 compliant. Didn't notice line 1 was 206.
 

CA_Punit

Well-known Member
Joined
Nov 18, 2019
Messages
941
Office Version
  1. 365
Platform
  1. Windows
This is what you want

Book1
ABCDEFGHIJKLM
2
3BranchCustomer NameAccount CodeOrder No.Order ValueDate CreatedDelivery DateAddress 1Delivery TypeVehicle RegDel PostcodePrimary BranchSecondary Branch
4168.00TestTestTestTestTestTestTestDeliveryDG64VUVG42206205
5168.00TestTestTestTestTestTestTestDeliveryDG64VUVG72168205
6168.00TestTestTestTestTestTestTestDeliveryDK66JZWPA73988
7168.00TestTestTestTestTestTestTestDeliveryDK66JZWEH53168205
8168.00TestTestTestTestTestTestTestDeliveryDG64VUVKA21205
9168.00TestTestTestTestTestTestTestDeliveryDG64VUVKA21205
10
11
12
13Branch NumberTotal DeliveriesNon ComplaintCompliant
14168624
15
16
17
18
Sheet2
Cell Formulas
RangeFormula
B14B14=COUNTIF(A4:A9,A14)
C14C14=COUNTIFS(A4:A9,A14,L4:L9,A14)
D14D14=COUNTIFS(A4:A9,A14,L4:L9,"<>"&A14)
 

Forum statistics

Threads
1,144,369
Messages
5,723,945
Members
422,527
Latest member
TotalBeginner201

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top