Information from a report

D

davidmor

New Member
Joined
Nov 20, 2020
Messages
30
Office Version
  1. 365
Platform
  1. Windows
HI

i wonder if anyone can help

i have a sheet, i can get the info from 2 of the columns i need (previous help) but cant get the data to pull from the others.

CustomerDespatch DateShipping ReqNo of palletsDestinationshipping refQtyDateCustomerDestination
Park Cakes Ltd Oldham12/08/20223558754Oldham3558754
Park Cakes Ltd Oldham12/08/20223438581Oldham3438582
Park Cakes Ltd Oldham12/08/20223438581Oldham34797826
Lindt & Sprungli UK Ltd12/08/202234797826Coventry3557775
Park Cakes Ltd Oldham12/08/20223557772Oldham35655315
Park Cakes Ltd Oldham12/08/20223557772Oldham3565901
Park Cakes Ltd Oldham12/08/20223557771Oldham3569073
Mars Wrigley Confectionery UK Ltd12/08/202235655315NELSON35690819
Park Cakes Ltd Bolton12/08/20223565901Bolton3569182
TATA CONSUMER PRODUCTS GB LIMITED12/08/20223569073Stockton-on-Tees3570116
Finlay Beverages12/08/20223569081Pontefract3570433
Finlay Beverages12/08/20223569083Pontefract3571131
Finlay Beverages12/08/20223569081Pontefract35716711
Finlay Beverages12/08/20223569081Pontefract3572712
Finlay Beverages12/08/20223569081Pontefract35729124
Finlay Beverages12/08/20223569081Pontefract35730914
Finlay Beverages12/08/20223569081Pontefract35731423
Finlay Beverages12/08/20223569081Pontefract35731520
Finlay Beverages12/08/20223569081Pontefract35731620
Finlay Beverages12/08/20223569081Pontefract3573208
Finlay Beverages12/08/20223569081Pontefract35732421
Finlay Beverages12/08/20223569082Pontefract3573465
Finlay Beverages12/08/20223569081Pontefract3573132
Finlay Beverages12/08/20223569082Pontefract3573362
Finlay Beverages12/08/20223569081Pontefract3573401
Fox's Biscuits Limited12/08/20223569182West Yorkshire3573431
Fox's Biscuits Limited12/08/20223570112West Yorkshire34792517
Fox's Biscuits Limited12/08/20223570112West Yorkshire34797926
Fox's Biscuits Limited12/08/20223570112West Yorkshire3491613
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
Sort by date Sort by votes
sorry just realised how poor my first sample was this ones better it has all the info required



Declarations sheet chep.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1CustomerDespatch DateShipping ReqNo of palletsDestinationshipping refQtyDateCustomerDestination
2Park Cakes Ltd Oldham12/08/20223558754Oldham3558754
3Park Cakes Ltd Oldham12/08/20223438581Oldham3438582
4Park Cakes Ltd Oldham12/08/20223438581Oldham34797826
5Lindt & Sprungli UK Ltd12/08/202234797826Coventry3557775
6Park Cakes Ltd Oldham12/08/20223557772Oldham35655315
7Park Cakes Ltd Oldham12/08/20223557772Oldham3565901
8Park Cakes Ltd Oldham12/08/20223557771Oldham3569073
9Mars Wrigley Confectionery UK Ltd12/08/202235655315NELSON35690819
10Park Cakes Ltd Bolton12/08/20223565901Bolton3569182
11TATA CONSUMER PRODUCTS GB LIMITED12/08/20223569073Stockton-on-Tees3570116
12Finlay Beverages12/08/20223569081Pontefract3570433
13Finlay Beverages12/08/20223569083Pontefract3571131
14Finlay Beverages12/08/20223569081Pontefract35716711
MASTER
Cell Formulas
RangeFormula
AA2:AA423AA2=UNIQUE(FILTER(LEFT(F2:F1000,6),F2:F1000<>""))
A2:A4,A6:A8A2="Park Cakes Ltd Oldham"
A5A5="Lindt & Sprungli UK Ltd"
A9A9="Mars Wrigley Confectionery UK Ltd"
A10A10="Park Cakes Ltd Bolton"
A11A11="TATA CONSUMER PRODUCTS GB LIMITED"
A12:A14A12="Finlay Beverages"
U2:U4,U6:U8U2="Oldham"
U5U5="Coventry"
U9U9="NELSON"
U10U10="Bolton"
U11U11="Stockton-on-Tees"
U12:U14U12="Pontefract"
AB2:AB14AB2=SUMIFS(I:I,F:F,AA2)
Dynamic array formulas.
 
Upvote 0
ok so ive got some formula in that works in another similar sheet, it normally pulls the date in for me, (Kudos to Fluff for that)

but for some reason on this sheet its not working correctly

Declarations sheet chep.xlsx
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAF
1CustomerDespatch DateShipping ReqNo of palletsDestinationshipping refQtyDateCustomerDestination
2Park Cakes Ltd Oldham12/08/20223558754Oldham3558754#N/A#N/A#N/A
3Park Cakes Ltd Oldham12/08/20223438581Oldham3438582#N/A#N/A
4Park Cakes Ltd Oldham12/08/20223438581Oldham34797826#N/A#N/A
5Lindt & Sprungli UK Ltd12/08/202234797826Coventry3557775#N/A#N/A
6Park Cakes Ltd Oldham12/08/20223557772Oldham35655315#N/A#N/A
7Park Cakes Ltd Oldham12/08/20223557772Oldham3565901#N/A#N/A
8Park Cakes Ltd Oldham12/08/20223557771Oldham3569073#N/A#N/A
9Mars Wrigley Confectionery UK Ltd12/08/202235655315NELSON35690819#N/A#N/A
10Park Cakes Ltd Bolton12/08/20223565901Bolton3569182#N/A#N/A
11TATA CONSUMER PRODUCTS GB LIMITED12/08/20223569073Stockton-on-Tees3570116#N/A#N/A
12Finlay Beverages12/08/20223569081Pontefract3570433#N/A#N/A
13Finlay Beverages12/08/20223569083Pontefract3571131#N/A#N/A
14Finlay Beverages12/08/20223569081Pontefract35716711#N/A#N/A
15Finlay Beverages12/08/20223569081Pontefract3572712#N/A#N/A
16Finlay Beverages12/08/20223569081Pontefract35729124#N/A#N/A
17Finlay Beverages12/08/20223569081Pontefract35730914#N/A#N/A
18Finlay Beverages12/08/20223569081Pontefract35731423#N/A#N/A
19Finlay Beverages12/08/20223569081Pontefract35731520#N/A#N/A
20Finlay Beverages12/08/20223569081Pontefract35731620#N/A#N/A
21Finlay Beverages12/08/20223569081Pontefract3573208#N/A#N/A
22Finlay Beverages12/08/20223569081Pontefract35732421#N/A#N/A
MASTER
Cell Formulas
RangeFormula
AA2:AA453AA2=UNIQUE(FILTER(LEFT(F2:F10000,6),F2:F10000<>""))
AC2:AC453,AD2AC2=INDEX(D2:D10000,MATCH(AA2#&"*",F2:F10000,0))
AE2:AE453AE2=INDEX(F2:F10000,MATCH(AA2#&"*",H2:H10000,0))
U2:U4,U6:U8U2="Oldham"
U5U5="Coventry"
U9U9="NELSON"
U10U10="Bolton"
U11U11="Stockton-on-Tees"
U12:U22U12="Pontefract"
AB2:AB22AB2=SUMIFS(I:I,F:F,AA2)
Dynamic array formulas.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Count total award value per unique supplier
Replies
4
Views
89
Fluff
Fluff
tazeo
  • Question Question
Hierarchical Table to a PivotTable (or something similar)
Replies
0
Views
63
tazeo
tazeo
T
  • Solved
Looking for VBA Code to Conditionally Format Time Tracker on Entry
Replies
3
Views
192
Tara_A_Data
T
T
  • Question Question
Using Excel to create a schedule table from given data table.
Replies
1
Views
865
Herakles
H
B
  • Question Question
VBA/Macro (dynamic ranges) IF And Statement that uses interior color in column A and blank in column F
Replies
0
Views
686
blooeyez
B

Forum statistics

Threads
1,223,902
Messages
6,175,278
Members
452,629
Latest member
SahilPolekar

Share this page

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