Hi there,
I'm trying to develop a spreadsheet for a small bakery I've started up. I've attached what I've done so far.
What I'm struggling with with is the DELIVERY bit (A23).
I want to be able to produce a Delivery Sheet so that I know the name of customer and type of bread ordered (see my concatenate in A25 as example) which I need to deliver to each delivery hub (listed as A-F in the top left of the spreadsheet).
Is there any way of doing this?
Many thanks,
Ian
Last edited by twigdip; 08-08-2013 at 08:28 AM.
i'd add a helper column see n11:n20 then use that to distinguish between the drop offs then use index/match to retrieve the data and join that together see attached
"Unless otherwise stated all my comments are directed at OP"
Mojito connoisseur and now happily retired
where does code go ?
look here
how to insert code
how to enter array formula
why use -- in sumproduct
recommended reading
wiki Mojito
how to say no convincingly
most important thing you need
Martin Wilson: SPV
and RSMBC
Hi Ian
Try in A25 and copy down and across, this ARRAY formula.
=IFERROR(INDEX($A$11:$A$20&" "&"("&$J$11:$J$20&")",SMALL(IF($E$11:$E$20=A$24,ROW($A$11:$A$20)-10),ROW(A1))),"")
In your profile you say that you use Excel2007. If not try to include the following formula to IF(iserror...
=INDEX($A$11:$A$20&" "&"("&$J$11:$J$20&")",SMALL(IF($E$11:$E$20=A$24,ROW($A$11:$A$20)-10),ROW(A1)))
-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
I've put this formula in N11:
Formula:
Please Login or Register to view this content.
and copied down. This sets up a unique sequential reference for each order and delivery hub, like A_1, A_2, B_1 etc.
Then I put this formula in A25:
Formula:Please Login or Register to view this content.
and this is copied across and down to retrieve each name in the appropriate cell.
Hope this helps.
Pete
Above post is not shown on New Posts list - difficulty posting.
Pete
alternative non array
=IF(ROWS($A$1:A1)>COUNTIF($E$11:$E$20,A$24),"",INDEX($A$11:$A$20&" ("&J11:J20&")",MATCH(LARGE(INDEX(($E$11:$E$20=A$24)*1/ROW($A$1:$A$10),0),ROWS($A$1:A1)),INDEX(($E$11:$E$20=A$24)*1/ROW($A$1:$A$10),0),0)))
Many thanks everyone.
Pete-UK: I like yours the best because it's really simple, but it doesn't combine the deliver drop off points with the name of the person: e.g. Fitz, Matt (WS) [ws being the type of bread in column J)
Is there a way of modifying your formula to add this?
Martindwilson: your formula is brilliant but sadly I can't seem to get it to work on on of our office computers which runs the (dreaded and clunky) OpenOffice Calc package!
Change the formula in A25 to this:
Formula:Please Login or Register to view this content.
then copy across and down as required.
Hope this helps.
Pete
Scrap that. I've got it to work!
Wow.
Many thanks MartindWilson.
Just one more thing (to complicate things!): Is there any way you can adjust the formula so that I can produce a different delivery table for every day of the week? (using data from column K?)
cant help that don't really do open office probably because it is considered an aarry by openoffice
but the original wordbook i posted does what you ask
I don't know for Open Office but i believe that my modified ARRAY formula does also this...Just one more thing (to complicate things!): Is there any way you can adjust the formula so that I can produce a different delivery table for every day of the week? (using data from column K?)
=IFERROR(INDEX($B$11:$B$20&" "&"("&$K$11:$K$20&")";SMALL(IF(($F$11:$F$20=B$24)*($L$11:$L$20=$A25);ROW($B$11:$B$20)-10);ROW(B$1)));"")
*Thanks for the rep* and your kind words.
Many thanks everyone. Great stuff!
You are welcome and thanks for the feed back.
As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thank you.
Sorry, have reposted as a new query as it's a slightly different issue!
Last edited by twigdip; 08-08-2013 at 08:28 AM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks