• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Matching up Multiple Sales Orders to the Actual Landed Sales

M

MakamND01

New Member
Hello,

I am trying to look at Tab one (which is the original Opportunity/Lead data) and compare it to Tab two (which is the landed sales revenue details). I have successfully been able to match the first item on the Oppty to a matching item on the sales data. However, I am stuck with getting to the second, third, etc items on the Oppty. Trying to get visibility to this so I can create pivot tables to get the bottom line of sale completions, dates from oppty, etc. But I really don't know how to get that info for the subsequent lines on the Oppty.


Please let me know if you can help. I am willing to hear and learn...


https://skydrive.live.com/redir.aspx?cid=32a5bb69f9f589f1&resid=32A5BB69F9F589F1!107&parid=32A5BB69F9F589F1!105&authkey=!AKDpgz_W-GJX8NM
 
Hi, MakamND01!


I was looking at your file and I noticed this few -at least- inconsistencies:

a) formulas in M26:M27 differ from other cells in column

b) same for S27:V27


Also, would you please write down a couple of examples of expected output, indicating cell addresses? It'd be helpful for a better understanding, without analyzing the whole workbook. Thanks.


Regards!
 
Hello, thank you for inquiring. Yes, I was playing with options on rows 26 & 27. I left them as options in case they would work better for whomever would help.


As far as outputs, I was looking at the possibility of seeing the Oppty ID's populated in columns S:V for the subsequent products.


For example, M27 indicates there are two Opportunities that have Exchange Server Standard noted for the client ID# 640447. I can easily get to the first Oppty ID by a vlookup in N27. But I don't know how to get to the second Oppty to show in S27. This small example data only has up to two, but my live data has up to 5, so I need to be able to find the subsequent Oppty IDs. From there, I can pull that Quote date, etc to see which Oppty influenced the sale, etc.


Hopefully that makes sense. I'm self taught Excel user, and usually dig till I figure something out, but this on has me stumped. Thank you for any help.


Marsha
 
Hi, MakamND01!


Tried to download your file again and link seems to be unavailable. Is still there?


Regards!
 
Nope, still there. Here, try again, please. https://skydrive.live.com/redir.aspx?cid=32a5bb69f9f589f1&resid=32A5BB69F9F589F1!107&parid=32A5BB69F9F589F1!105&authkey=!AKDpgz_W-GJX8NM for some reason it only recognizes part of it as 'blue' and the rest in black. Might have to select all of it and paste separately in a new browser to get it to work
 
Hi all,


Have downloaded the sheet but the tabs your referring to are not the same? Why you are using countif() in column M in second sheet? How will the formula came to know that it has to select the first one or the second one?? Since both are the same??


Regards,

Faseeh
 
It was just a shot in the dark to see if it would help me. :)disregard the formula and field if it doesn't help. Obviously in over my head, so trying and failing certain things.


First tab is H=Oppty.....this is the quoted data

Second tab is MSS-.....this is the landed sales data


If I need to attack a different way, I'm open to hearing it. Self taught, so obviously something that I look at one way, could be easier a different way.


thank you all for your help. :)

Marsha
 
Hi, MakamND01!


Don't worry, Skydrive always behaves like that, thanks for the advise, we regularly copy & paste the whole URL.


Regards!
 
Hi, MakamND01!


Give a look at this file:

http://dl.dropbox.com/u/60558749/Matching%20up%20Multiple%20Sales%20Orders%20to%20the%20Actual%20Landed%20Sales%20-%20PX-REFRESH---help-1%20%28for%20MakamND01%20at%20chandoo.org%29.xlsx


I just slightly modified column C in first sheet to add a "_NN" suffix to the previous formula, with NN being a frequency index (bah, a counter).


In second sheet I modified N26:O26 and S26:V26 (red cells, and discard N25, please) so as to:

- look up with previous argument plus "_NN", with NN given from 2nd thru dash-minus-1 of cell in row 1 on each column

- unified the searching range within first sheet to $C:$Z


Hope it helps. Just advise if any trouble.


Regards!
 
Thank you. :) That seemed to work.


I'm either getting closer, or totally confusing myself. LOL Maybe I can ask how you'd do it!?


My goal. to be able to create a pivot that shows the sales that landed for specific products after the Opportunity was created for that product and customer ID. I think I'm overthinking it. I have it created using some vlookups, but that only links to the first product found when looking for that customer ID number. How would you recommend my getting visibility/reportablitiy to the subsequent products.


My second (or thirty ninth!) thought was to see if I could somehow list the products horizontally, maybe that would work, but I don't know how to do that either; and if would even work for my pivot.


So alas, I'm feeling like a dog chasing her tail and wishing it would stop moving!! Ugh. Any patient, even pitying, help would be appreciated. :)


https://skydrive.live.com/view.aspx/PX-REFRESH---help.xlsx?cid=32a5bb69f9f589f1&app=Excel#!/view.aspx?cid=32A5BB69F9F589F1&resid=32A5BB69F9F589F1%21108
 
Hi, MakmdND01!


Glad to see it worked for you. Getting in trouble again when intended downloading. Again, still there, authorization Ok, and so on?


Regards!


PS: I'm not sure to suggest the dog to stop it moving ;)
 
Okay, here is a better link (I hope). :)

https://skydrive.live.com/redir.aspx?cid=32a5bb69f9f589f1&resid=32A5BB69F9F589F1!108&parid=32A5BB69F9F589F1!105&authkey=!AHnfwyRHyp0TRpk


Thank you for your help. Just think I'm going at things the wrong way and complicating myself into places I don't need to go to get this silly data to show on a pivot table.


Thank you. (slight wag of tail, as I fell into an exhausted heap)

Marsha
 
Hi, MakamND01!

You've changed the worksheet from previous version, and I don't find the multi-oppties (5) across in a row. Would you be as kind as writing down an example of what you need taking a couple of rows in your new workbook? So as I can avoid learning again your business rules.

Keep on moving.

Regards!

PS: BTW... I didn't find any Oppty for Visual Studio 2010 Ultimate (preferable in Spanish) with free upgrade to 2011 recently anounced as Beta... which PromoCode should I use to get it? I won't care if there's a bundle that includes SQL Server 2012... :)))
 
Sorry about the change. Part of my whirling trials to get to the end. Okay, here is a new link. i've expanded upon the one you'd sent to me. Ive addd a cpl o column to see how the oppt date compares to the sales date. Then I've added a pivot to show a bit of where I'm going. I'd like to include the ones that also match the second opportunity, but don't know how. For example, I've highlighted a row that the first Oppty column date is past the sales date, but the second Oppty colmn date works because it is before the sales date. But don't know how to work it into my pivot. Make sense?


https://skydrive.live.com/redir.aspx?cid=32a5bb69f9f589f1&resid=32A5BB69F9F589F1!109&parid=32A5BB69F9F589F1!105&authkey=!ANsHGIFuYmPoKSI


Thanks :)

I'll have to check on your question next week. :)

M
 
Hi thank you for your help. I'm wondering if you've been able to check out the adjusted workbook with a pivot now.... :)

Thanks!
 
Hi, MakamND01!


Yes, I've downloaded it on Friday night but only today I could give a look inside it. I fulfilled sheet 2 (MSS-ALLTPIDs) with the formulas, then went to sheet 3 (PIVOT) and everything looks fine for me. So I came back and read upwards what was your goal with the PT and I think you achieved it. I don't find anything to modify. Am I missing to see something as big as an elephant within a pet shop?


Regards!


PS: I asked for a second opinion. Keep you informed. :)
 
Hello SirJB7. Thank you so much for your help. It was great to get your input and assistance. I did, indeed, get to the point I wanted to. And was able to find the data I needed to report on. I'll be putsing more on it, just because I want to dice it a different way. But wanted to thank you! :)


I don't know If I can help you with your question on purchasing, but I'll send it to a couple of reseller partners I know and see if I can help.

Have a great rest of the week.

Marsha
 
Hi, MakamND01!


Thanks for your feedback, welcome back whenever needed or wanted, and the most important at all: keep on moving ;)


Regards!
 
Hi, MakamND01!


Here is the result of my ask for a second opinion:

http://dl.dropbox.com/u/60558749/Matching%20up%20Multiple%20Sales%20Orders%20to%20the%20Actual%20Landed%20Sales%20Revised_Reviewed%20%28for%20MakamND01%20by%20Faseeh%20at%20chandoo.org%29.xlsx


Give a look at it, maybe you find useful Faseeh's approach. BTW, I agree with him about selected highlight colors.


Just advise, just in case.


Regards!


@Faseeh


Hi!

Received, checked, thanked, delivered.

Regards!
 
Hi SirJB7,


You are welcome any time!


@MakamND01!


This web-page discusses designing spreadsheets just go through their standards they are really helpful.


http://www.ssrb.org/


Regards,

Faseeh
 
You must log in or register to reply here.
Back
Top