HELP - Extract data from table (confusing)

monkeyharris

Active Member
Joined
Jan 20, 2008
Messages
346
Office Version
  1. 365
Platform
  1. Windows
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

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.
Can you share your expected result from the above data.
 
Upvote 0
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
 
Upvote 0
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.???
 
Upvote 0
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).
 
Upvote 0
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
 
Upvote 0
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)
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

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
Back
Top