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

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
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)
Going to try this right now. Looks great.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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