Is this possible?

jonesymw

New Member
Joined
Aug 28, 2007
Messages
9
I am trying to figure out a way to tell excel to view my spreadsheet and say that stores a, b, c, d, e, f, g, h, i should be listed as region 1 under the region tab column. Then if the store is not listed, add it to sheet with the store name, store number, region code, and 0 for sales reported. Then if there is a store listed that is not in the macro change the font to red (so I know to add it). And finally sort it by region tab, then store #.
sales.xls
ABCDE
1RegionTabSTORENUMSTORENAMESalesRegion
2Region1AJonesy11MD-A1
3Region1BJonesyPlus13MD-A1
4Region1CJonesySuperstore11MD-A1
5Region1DJonesyCaf12MD-A1
6Region1EJonesyBarandGrill12MD-A1
7Region2AAJonesy21MD-B1
8Region2ABJonesyPlus21MD-B1
9Region2ACJonesySuperstore21MD-B1
10Region2ADJonesyCaf22MD-B1
11Region2AEJonesyBarandGrill21MD-B1
12Region3BAJonesy31MD-C1
13Region3BBJonesyPlus32MD-C1
14Region3BCJonesySuperstore32MD-C1
15Region3BDJonesyCaf31MD-C1
16Region3BEJonesyBarandGrill31MD-C1
17Region4CAJonesy42MD-C4
18Region4CBJonesyPlus41MD-C4
19Region4CCJonesySuperstore41MD-C4
20Region4CDJonesyCaf41MD-C4
21Region4CEJonesyBarandGrill41MD-C4
Sheet1
 

Some videos you may like

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
Generally, this forum helps Excel users to solve their problems by viewing either the formulas or code the user provides, and then indicating where their problem might lie. Normally, no one here will try to solve a total problem, such as the one you are describing. That's probably why you have had so many viewers, with nobody trying to answer you. Also, the posting instructions clearly requires you to very briefly indicate the type of Excel problem you are facing, and frowns heavily on subjects such as your enigmatic "Is this possible?"!
 

jonesymw

New Member
Joined
Aug 28, 2007
Messages
9
Clarification

I am not asking anyone to solve the issue at hand. I was simply asking if anyone could simply say by hearing the problem, oh try this function or this command. I just had no idea where to begin and was asking if someone could say oh try this function, not to say oh here is your script from beginning to end. I apologize if everyone got that opinion. As far as the subject line goes - I would have had to of made it one rather large line to put everything I wanted to in it - so that is why it was so "enigmatic" with, "Is this possible?" because that is basically all I wanted to know - is it possible with basic excel functions and etc to do what I want to do so that I don't have to waste hour upon hour running in circles to find out that it wasn't. So hopefully this helps clarify that I'm not not looking for a hand out.
 

jonesymw

New Member
Joined
Aug 28, 2007
Messages
9
Any Pointers for Verifying, Adding, Sorting Data?

I am trying to figure out a way to tell excel to view my spreadsheet and say that stores a, b, c, d, e, f, g, h, i should be listed as region 1 under the region tab column. Then if the store is not listed, add it to sheet with the store name, store number, region code, and 0 for sales reported. Then if there is a store listed that is not in the macro change the font to red (so I know to add it). And finally sort it by region tab, then store #. I am not looking for someone to hand me the script. I just am wondering if it is a possibility and for a few suggestions on what funtion to try to manipulate or just a hey check into this, research this topic - it will lead you in the right direction. Then I can get to the point where I have script for hashing. Appreciate any pointers.
sales.xls
ABCDE
1RegionTabSTORENUMSTORENAMESalesRegion
2Region1AJonesy11MD-A1
3Region1BJonesyPlus13MD-A1
4Region1CJonesySuperstore11MD-A1
5Region1DJonesyCaf12MD-A1
6Region1EJonesyBarandGrill12MD-A1
7Region2AAJonesy21MD-B1
8Region2ABJonesyPlus21MD-B1
9Region2ACJonesySuperstore21MD-B1
10Region2ADJonesyCaf22MD-B1
11Region2AEJonesyBarandGrill21MD-B1
12Region3BAJonesy31MD-C1
13Region3BBJonesyPlus32MD-C1
14Region3BCJonesySuperstore32MD-C1
15Region3BDJonesyCaf31MD-C1
16Region3BEJonesyBarandGrill31MD-C1
17Region4CAJonesy42MD-C4
18Region4CBJonesyPlus41MD-C4
19Region4CCJonesySuperstore41MD-C4
20Region4CDJonesyCaf41MD-C4
21Region4CEJonesyBarandGrill41MD-C4
Sheet1
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829

ADVERTISEMENT

Ignore!
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829

ADVERTISEMENT

I didn't mean to offend, so, I apologize for my bluntness.

Referreng to your first sentence, namely,
I am trying to figure out a way to tell excel to view my spreadsheet and say that stores a, b, c, d, e, f, g, h, i should be listed as region 1 under the region tab column.
perhaps the following pseudocode concept will help you?
Code:
For i = 2 to the last row with data
  If len(cell "B" & i) = 1 then
    show the sentence, "Store "& CHAR(j) &" should be listed as Region 1"
  ElseIf len(cell "B"& i) = 2 then
    For j = 65 to 90 'Code("A")=65, Code("Z") = 90     
      If left(cell "B" & i,1) = char(j) then
        show the sentence, "Store "& cell "B"& i(char(j)" should be listed _
        as  Region "& j-63
      End If
    Next j
  End If
Next i
Is this the kind of help you are looking for? If so, maybe I can help. But, if you need VBA help, I know very little VBA, and someone else would have to pitch in.
 

jonesymw

New Member
Joined
Aug 28, 2007
Messages
9
Thankful for any help!

That is the type of help I am looking for, even if it isn't very specific it will be a help. Just an idea for me to try or a pointer here and there. My only problem is with this particular challenge, I just had no idea where to begin. I will try working with your example and see what I come up with, thanks.
 

RalphA

Well-known Member
Joined
May 14, 2003
Messages
3,829
jonesymw:

In re-reading my post, I notice that I have one or two errors. But, if you can get the help you needed from my post, then I won't try to correct my code. If you get tangled up, though, I will carefully correct my previous post. Good luck!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

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
Top