I need to do the following and need HELP!!! I have a massive…

http://wikisend.com/download/931564/Excel document I need help with.xlsx

Here is one....

http://wikisend.com/download/594560/Publisher file I need help with.pub

This is the publisher file that I mail merge the 46 markets then I need to pull each individual sales data into field on the publisher file... Does that make sense?

If you would like to talk real quick so I can explain, XXX-XXX-XXXX

tks

http://wikisend.com/download/930888/Main zip list for weather.xlsx

This is the main zip list that outlines the markets I need. I use this doucment to merge into 46 seperate pages in Publisher then I will need to merge sales data into bottom left corner on each market page.

That is great... The info by market that I need is the column labled POS 1 (top 3 in each market) and variance (last column) top 3 in each market.

Thanks so much!!!

OK, I am sorry... I had to do a Vlookup and did not label the columns correctly.

on the excel doc I need help with is a tab called primarybystore. T

Column A is the correct market number that should coralate with the other document that has the market, city, state, zip on it. The format I need it pulled into is below. A seperate one of these for each market on main zip list document using numbers from the excel doc I need help iwth... Does that make sense?

I don t think this looks right...

I need to know by market (column A that is not labled in your sheet - starts with #34) - top 3 stores in sales (POS 1) I will upload a final result for you to see...

Here is what the end result needs to look like for each of the 46 markets on the main zip for weather sheet...

http://wikisend.com/download/505436/Sales-Comp numbers for MW.xlsx

You dont see a reference because I have to make 46 different pages - each specific to that market so it is a given that in the page for Market 16 is Market 16 sales data...

I know this is crazy and confusing but I really appreciate it.

I have to do this project weekly so the numbers will change each week and I will have to do this every Sunday night so I am trying to figure out a way so I don't have to spend 2 hours sorting by market then highlighting the top 3 and pasting into that excel file... But maybe that will be the quickest .... I am not sure

if you are talking about the exact numbers, I think that came from a couple of weeks ago when I was playing... Just remove the number data and just look at the fields -

I need the three sections

Top sales for week xxx

Top comps (which is variance on excel file i need help with)

The top sellers will be the same for each market so no issue there...

So, I need to extract the top sales (top 3 by market), Top comp or variance by market.

Here is the link of the sample information on the same page...

http://wikisend.com/download/457750/Excel document I need help with1.xlsx

This looks wonderful... How hard is it to only use the top three stores in the end result data?

How can I figure out how to do this? Can I save this macro and use each week when I get the new week of data?

It is not just the first three stores... for the first section where it has top sales for week - it is the top 3 stores in sales so it would need to be sorted by POS1 then the top three in sales... Same with what they say is comp which is variance on the main sheet...

I outlined this in one of the first emails but it probably got lost in the confusion...

Thanks so much for your help!!!

I will... What type of comensation are you talking about...

In the above message, Thu, Mar 18, 2010 5:56 PM EST, I stated that this was part of the requirement... sorry that was missed....

Thanks

About the only thing I would like is to remove the extra row under sales... then it looks perfect!!!

If we wanted to compensate you, how is that done?

John,

I had to do one week to see what modifications need to be done before I submit for the bonus. I have to cut and paste this information each sunday night into a publisher file with market specific data in all 46 markets. I need some slight modifications to the macro so that it does not take me so long to format each one (this took me over 4 hours to do last night) so anything to make that shorter would be appreciated.

I have attached the link below with the exact look of what I need...

• Font needs to be Calibri
• The data font needs to be left justified and 11pt
• The header needs to be 12pt and centered
• The color of the headers needs to be the orange color (or as close to as possible)
• The percentage field needs to be 0 places after the decimal point
• When there is a field under the comp or variance section that does not contain a number, it needs to default to the next one with a number. There were some cases where there was a DIVD or some sort of a error message. That happens where there are no sales in POS2 so just ignor that and move to next applicaple with numbers.

I also need to figure out how I can directly import this information into my Publisher file without cutting and pasting... Do you know how to do that?

Thanks again for all your help

Lori

http://wikisend.com/download/965748/Sales-Comp numbers for MW.xlsx

John,

I did not add a new block - in my message above, I outlined what that was... That is a section that stays the same for all stores so that is why I did not include it for this... Back in the very begining, I outlined all this stuff but I think that is one of the difficulties working via the internet..

You have that last section correct about the comp field. I just needed it to not express the .00% after.. But it is right in this version.

The last thing is the column width of the third section under the description field needs to be bigger and the size field needs to be smaller like the state or something...

Does that make sense?

John,

This is what it needs to look like... As you can see, the last section of numbers that you just added have different needs for the cell size... Let me know your thoughts

http://wikisend.com/download/501472/Example for John.docx

well that stinks... I guess I will just have to keep it like I did and paste two different sections in sales area...

What about the question about publisher? Do you know how to like or merge this data into a publisher file?

I will send you the PDF version so you can see what the end result needs to be...

It just does not quite fit when I paste into the section. I need to go to the corner and shrink which them makes some rows into two rows which makes the section longer and does not fit...

What if we remove the state column? Will that give us more room?

Here is what I do today in publisher - see the common fields on each market page such as date, week of publication, zip code above the weather - that is a mail merge function taken from an excel main zip list (which I think I sent you a while ago). The I merge the publication and it makes 46 tabs in publisher. Then I go in a get the weather for each zip and paste into publication (all 46 pages). Then I go into the excel file you have been working on and filter the info by market and then sort my each field I need such as top sales and comp (variance). Can you see my frustration... It takes hours and I am just not smart enought with both products to know what you know.. so it takes me forever and I take time away from my kids on a Sunday to do this report... Can you tell I am frustrated...

Here is the file...

Thanks

http://wikisend.com/download/682090/John PDF.pdf

Not sure what you mean by your last posting... I replyed two times above... i have pasted it below... Does that answer your question?

---------------------------------------------

It just does not quite fit when I paste into the section. I need to go to the corner and shrink which them makes some rows into two rows which makes the section longer and does not fit...

What if we remove the state column? Will that give us more room?

Here is what I do today in publisher - see the common fields on each market page such as date, week of publication, zip code above the weather - that is a mail merge function taken from an excel main zip list (which I think I sent you a while ago). The I merge the publication and it makes 46 tabs in publisher. Then I go in a get the weather for each zip and paste into publication (all 46 pages). Then I go into the excel file you have been working on and filter the info by market and then sort my each field I need such as top sales and comp (variance). Can you see my frustration... It takes hours and I am just not smart enought with both products to know what you know.. so it takes me forever and I take time away from my kids on a Sunday to do this report... Can you tell I am frustrated...

I am asking my boss about removing the state and let you know...

How do you do the copy as a pic thing?

OK, he said he would rather have it in so lets reduce the size of the font to fit that exact PDF I sent to you.

I guess we should see if reducing the font makes any difference and if not, look into the image thing you said above.

Lori

I think we should reduce the font and make some adjustments like instead of State - use St and make the font 9 & 10 instead of the 11 & 12.

What do you think?

Hi John,

I tried it on Sunday just as it is and it works fine... Saved me time so thanks!!!

I was just thinking of ways we could include the third section (the one that is consistant for all 46 markets..). If we attach it as a picture under your macro file, would that work? We know how big it is going to be each week...

Right now, I have to cut and paste your macro into each of the 46 markets, then I have to cut and paste the third in and allign properly so they don't look like such seperate sections...

Just a thought... What do you think?

Here is a sample of what I am talking about.... It does not come out the best but just an idea... What about a hyperlink?

http://wikisend.com/download/455150/Lori Test.xlsx

That would be better but I did not think you could do that because the cell sizes are different then the table above... If you can, that would be great!!!

Then I could populate the five rows each week of the data..

Perfect.

Lori

John,

This will not work... This is what we ran into the first time you tried... The city cell in the first two sections is too small, the city name will not fit...

What if you switch the city ans state field in the first two - then I think the cell size will be the same with the bottom section description field...

Do you know what I mean?

Look on market 16 tab... I rearanged it...

http://wikisend.com/download/851310/Copy of Market 6.xlsm

The Lori test file was a sample where I was using an image as the third section, not actual excel text. I inserted a picture then reduced the size to fit... This was an example of my idea of importing a picture...

Look at the file I sent a few minutes ago where I moved the city and state column in the first two sections... I think that should work...

John,

As I said when I sent the lori test file - I was trying to think of other ways to get the third section in with your macro so I did not have to spend time cutting and pasting two seperate sections and trying to make them look even... If you read back you will see....

In the copy of market 6 I sent back to you - I had the idea of switching the state and the city cells so they would be the same as the third section below....

So, if you are going to move the city and the state so that it matches the third section below, then yes, please do it.

Thanks

Not just the copy of market - I made the change in market 16 so the first tab... Do you see that?

Looks great but the comp % is a different font...

Now where do I go to change the 5 rows of data in the third section that will stay the same each week?

I am sorry but the fonts are still not the same... In some sections in the body, they are 12 and some are 11. The Comp % is still Helv 12....

I would like all body to be 11 and the headings to be 12... like you have in some...

OK..

First if I am looking at your copy of Market 8 and I am looking at your macro part primary by store tab.

AA5, AB5,AC5,AD5 in 11 font and this is correct. AA8-AD8 should be 11 - AA11-AD11 should be 11 font as well.

Comp % in cell AD9 is in HELV font and sould be CalaXXXXX XXXXXke the rest of the sheet.

AA9 is 12 font - should be 11 to be consistant with rest of file.

I dont' mean to be picky but it needs to be consistent throughout the whole file...

OK..

First if I am looking at your copy of Market 8a and I am looking at your macro part primary by store tab.

AA5, AB5,AC5,AD5 in 11 font and this is correct. AA8-AD8 should be 11 - AA11-AD11 should be 11 font as well. All header rows should be same font size.

Comp % in cell AD9 is in HELV font and sould be CalaXXXXX XXXXXke the rest of the sheet.

AA9-AD9 is 12 font - should be 11 to be consistant with rest of file.

You know, I do not appreciate your tone... If you want to be technical about the whole thing, way back in the begining when I was origionally asking how we could incorporate the third section under the first two and you said it could not be done because the cell sizes are different... Why did you not mention switching the state and city like I suggested in this last go round??? Instead, you spent time doing something and now we are back changing again... I was the one who came up with the switch and when I said go with what I had sent, I meant the switch of the city and state as I talked about in the note. You made the changes to the Helv font... Not me, I would think you would look for consistancy in your work - not me double checking each cell to make sure it is correct...

Then to get the smart comments back is not helpful!

Thanks for your time

No... Don't bother...

Thanks for your help