#### Problem with data using IF and Nested IF statements possibly???

```Afternoon All
I am attempting to analyse data from multiple worksheets from numerous
people the incoming data all has one thing in common column A this is a
certain frequency a job is done. The problem is that there are many ways of
entering the data ie 12 months or 52 weeks or 365 days all essentially
meaning the same thing.
My idea is to collate the data in col A and then using the Helper column as
the standard frequencies ie if cell A1 = 12 months, closes frequency in
helper is 52 weeks therefore value in C1 = 52 weeks. By using an  IF
statement I can change the value of one frequency as above however when it
comes to manipulating all of the different possible values in A I get a bit
lost. This may not be practical if so please let me know and I will get some
sleep
Ajay

Col A                        Helper Col       Col C
12 Months	                4weeks          52 weeks
182 Days	                8weeks
24 Months   	12 weeks
26 Weeks  	13 weeks
28 Days	                17 weeks
36 Months	                26 weeks
3 Months	                 52 weeks
4 Months               	104 weeks
52 Weeks          	156 weeks
6 Months
90 Days

```
 0
Ajay (74)
12/8/2004 4:05:06 PM
excel.misc 78881 articles. 5 followers.

2 Replies
680 Views

Similar Articles

[PageSpeed] 44

```Hi
provide us with your exact mapping rules and the source values.
Probably a VLOOKUP formula would do. See:
http://www.contextures.com/xlFunctions02.html

--
Regards
Frank Kabel
Frankfurt, Germany

"Ajay" <Ajay@discussions.microsoft.com> schrieb im Newsbeitrag
news:738CFBB1-7636-41B3-8958-A690CD3541B1@microsoft.com...
> Afternoon All
> I am attempting to analyse data from multiple worksheets from
numerous
> people the incoming data all has one thing in common column A this is
a
> certain frequency a job is done. The problem is that there are many
ways of
> entering the data ie 12 months or 52 weeks or 365 days all
essentially
> meaning the same thing.
> My idea is to collate the data in col A and then using the Helper
column as
> the standard frequencies ie if cell A1 = 12 months, closes frequency
in
> helper is 52 weeks therefore value in C1 = 52 weeks. By using an  IF
> statement I can change the value of one frequency as above however
when it
> comes to manipulating all of the different possible values in A I get
a bit
> lost. This may not be practical if so please let me know and I will
get some
> sleep
> Ajay
>
>
> Col A                        Helper Col       Col C
> 12 Months                 4weeks          52 weeks
> 182 Days                 8weeks
> 24 Months   12 weeks
> 26 Weeks  13 weeks
> 28 Days                 17 weeks
> 36 Months                 26 weeks
> 3 Months                  52 weeks
> 4 Months               104 weeks
> 52 Weeks          156 weeks
> 6 Months
> 90 Days
>

```
 0
frank.kabel (11126)
12/8/2004 5:21:09 PM
```Morning Frank,
Not entirely sure what your after Frank; If one looks at Column A below I
want to convert them all into weeks format to standardise the data from the
different sources. Eg 6 Months and 182 Days (Col A) will become 26 Weeks, 28
Days will become 4 weeks follow this format for all the values in col A below.
12 months = 52 weeks, 90 days and 3 months = 13 weeks, 24 Months = 104
weeks, 4 months = 17 weeks, 36 months = 156 weeks.
Ajay

"Frank Kabel" wrote:

> Hi
> provide us with your exact mapping rules and the source values.
> Probably a VLOOKUP formula would do. See:
> http://www.contextures.com/xlFunctions02.html
>
> --
> Regards
> Frank Kabel
> Frankfurt, Germany
>
> "Ajay" <Ajay@discussions.microsoft.com> schrieb im Newsbeitrag
> news:738CFBB1-7636-41B3-8958-A690CD3541B1@microsoft.com...
> > Afternoon All
> > I am attempting to analyse data from multiple worksheets from
> numerous
> > people the incoming data all has one thing in common column A this is
> a
> > certain frequency a job is done. The problem is that there are many
> ways of
> > entering the data ie 12 months or 52 weeks or 365 days all
> essentially
> > meaning the same thing.
> > My idea is to collate the data in col A and then using the Helper
> column as
> > the standard frequencies ie if cell A1 = 12 months, closes frequency
> in
> > helper is 52 weeks therefore value in C1 = 52 weeks. By using an  IF
> > statement I can change the value of one frequency as above however
> when it
> > comes to manipulating all of the different possible values in A I get
> a bit
> > lost. This may not be practical if so please let me know and I will
> get some
> > sleep
> > Ajay
> >
> >
> > Col A                        Helper Col       Col C
> > 12 Months                 4weeks          52 weeks
> > 182 Days                 8weeks
> > 24 Months   12 weeks
> > 26 Weeks  13 weeks
> > 28 Days                 17 weeks
> > 36 Months                 26 weeks
> > 3 Months                  52 weeks
> > 4 Months               104 weeks
> > 52 Weeks          156 weeks
> > 6 Months
> > 90 Days
> >
>
>
```
 0
Ajay (74)
12/9/2004 9:23:02 AM

Similar Artilces:

MS Money 95 data files
I hope that some one can answer this for me. I have used MS Money 95 for years, and it works just fine for me on Windows XP, however, I now have to reformat my hard drive, and have discovered that I can nolonger find my original install disk. Will the latest versions of Money still read the MS Money 95 data files. All that I have ever used the program for is to track my investments, and am unlikely to do any different in the future. Thanks Stan B In microsoft.public.money, Stan Banner wrote: >I hope that some one can answer this for me. >I have used MS Money 95 for years, and...

Using expression builder object
Hi, I'm developing a wizzard in Access which builds import templates for various data sources to a fixed set of tables. In step 3 the users must be able to build an expression ; for instance Left([Fieldx],20) . Now I would like to have a command button on my form which calls the Access expression builder to allow the users to use this to build the expression. This expression will then be stored in a text box linked to the templates table. Anybody know how to call and use this object from VBA code? -- Kind regards Noëlla DoCmd.RunCommand acCmdInvokeBuilder I th...

I am currently having a big issue with sql deadlocking on the PrincipalObjectAccess table. The last few months I have been working on a synchronization process using a Biztalk orchestration. The sync uses the crm webservices to create and update account and contact records in CRM. But now deployment to the production environment gives me some problems. It seems that when trying to update account records (which is one of the first actions in the sync process) the webservice gives me Generic SQL errors and SQL timeouts. After extensive profiling and tracing in SQL I found that there are...

Parsing data from one spreadsheet into another format
The data that we dump out of one machine comes in like below. %AT_1300 Bottoms|Conductivity| (Water Out) InputRange VDC1to5 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Low 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) Custom_Range_Hi 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MinScale 0.0 %AT_1300 Bottoms|Conductivity| (Water Out) MaxScale 20.0 %AT_1300 Bottoms|Conductivity| (Water Out) EngUnits mhos %AT_1300 Bottoms|Conductivity| (Water Out) StepResponseTime 1.0 %AT_1300 Bottoms|Conductivity| (Water Out) DigFiltTimeCnst 0.016 And I need to convert this data to this f...

Using part of a cell in a chart title
I have a chart which should get a title. However, this should be partly be used from a cell e.g. "counted with 5%" 5% should be taken from the cell and used in the title. Is this possible? Hi, Yes it's possible but all of the chart title needs to be in the cell. So you may need to use a helper cell and concatenate text and value. http://www.andypope.info/tips/tip001.htm Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Nicole" <Nicole@discussions.microsoft.com> wrote in message news:5CB7A971-AA7F-4C34-BB42-7DC283AA2958@micro...

docmd.transfertext problem
Hi, I am using access 97 and tried to import a csv file to the mdb table. I run a code as following: DoCmd.TransferText acImportDelim, "Specification4", "input", DEFAULT_PATH & "online.txt", 1 In online.txt, there is a field which is 10 digit number and I specified it as a double datatype in the specification4. After the import, I found out that the 10 digit number data in the field get empty in the destined table while other fields are all right. Therefore I import manually using specification4 instead of running code. This time the 1...

Exchange update problem
I have tried to upgrade exch2k3 sp1 to sp2, but the update fails with "the file pcproxy.dll is in use, and setup cannot identify the app or srvc. setup cannot continue" Any clues/ideas/suggestions? Please. -- ----------------------------------------------------------------------------------------------------------------------- This message has been checked for all known viruses. The information contained in this e-mail and any attachments is confidential and may be the subject of legal, professional or other privilege. It is intended for the named addressee only and may not ...

Transferring over outlook data to new XP machine
How do I transfer over my old emails, address book to my new XP machine? I have looked over the internet and found nothing the tells me EXACTLY how to do this, any help would be greatly appreciated. senior_tech@yahoo.com If your using MS Outlook copy your .PST file across and import it into the new install. >If your using MS Outlook copy your .PST file across and import it into the new install. No, don't import it. Simply use "File">"Open" -- Brian Tillman Smiths Aerospace 3290 Patterson Ave. SE, MS 1B3 Grand Rapids, MI 49512-1991 Brian.Tillman is the nam...

data input in text box
We have a form which the operator enters data in a text box. Currently we have a 'done' button on the form that the operator clicks to send the text box info to a vba program. How can we send the text box info to the vba program when the operator hits the enter key @ the end of the data entry for the text box? TIA -- _______________________________ In Christ's matchless name ted & colleen n6trf kc6rue Use the control's AfterUpdate event. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "ted" <n6trf@arr...

How do I use traffic lights in excel
I am wanting to use traffic lights in excel that change colour based on the result of a variance cell, ie if the result of the cell is 10 make the traffic light green, if it is 20 make the traffic light amber, if the result is 30 make the traffic light red. How do I do this? Shorty Format>Conditional Formatting>Cell Value is: Note: you can add up to 3 conditions(4 if you count default) Gord Dibben Excel MVP On Wed, 22 Dec 2004 16:35:03 -0800, Shorty <Shorty@discussions.microsoft.com> wrote: >I am wanting to use traffic lights in excel that change colour based on the &g...

multiple Domain name delivery problem
hi, I currently have Exchange Server 2003 Build 7638:2 SP2,. We have multiple domain names being delivered to the exchange store. I have nothad any problems, but i currently have one user that is not receiving emails with attachments from one certain "internet" sender to one of her email addresses, but the other address works fine & if they send emails without attachments, everything works fine from either address. I have had the user send them message with attachments to me & the user with the problem & i get the message, but not the other user! I even use message...

Filter recordset using query results
Hi all I have a form based on a query called [qry Quarterly Planning], it lists all Itineraries on the system. On this form you can filter records by specifying a Start and End Date for the [ReviewDate] and/or [Specialist]. It is a subform on a main unbound form, lets call this Subform1. Along side this I have another subform (Subform2) which displays ReviewDates that exist against an Itinerary. In other words Subform1 has a start date of an activity and if the activity lasts longer than 1 day, then the additional dates are stored in Subform2 (ItineraryDates). Currently when I...

Sorting Data #5
Is there formula or anyway to be able sort the below data into a format that I could create a pivot table on? I spend to many hours doing this every month. Invoice #: 12345 Invoice Date: 1/16/1950 A/P Code: ABC Due Date: 1/16/1950 Total Payable: \$100.00 Reference: Freight: Account #: 1234 Description: Name Reference 1 Amount: \$100.00 Account #: 4321 Description: Name Reference 2 Amount: \$100.00 Account #: 9876 Description: Name Reference 3 Amount: \$100.00 Any help would be much appreciated!! You need to show a Before and After version. You still might not get any help, but your ...

I got a new computer about six months ago that came with Windows Vista Home Premium 64bit. Before that I had done all of my .NET development either on an XP Pro VM or my former XP Pro computer at home. Shortly after getting my new computer at home, I also got a license for VMWare to be able to test my software on multiple platforms and configurations. I had wrote an application originally in VB.NET that was a simple backup utility. It supports mutiple backup configurations. Any given copnfiguration would define a backup which would be a list of files to backup, a list of folders to ...

HELP! Need to export hourly sales data on POS (NOT RMS)
How can I export hourly sales data across a date range? For instance, I want to show hourly sales for the month of October so I can graph it and post it in our break room. If I can't export hourly data, can I export daily sales? The built-in reports don't address this data format. This is a multi-part message in MIME format. ------=_NextPart_000_008E_01C826DC.CBC512D0 Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response Content-Transfer-Encoding: 7bit Mark, This should work for you. Keep in mind it takes up to 5-10 minutes to load...

How do I merge cells with multiple data values?
I've tried highlighting the two cells which are in the same row. It suggested that I format and align. Both of which I've tried. I keep getting the same error message, "The selection contains multiple data values. Merging into one cell will keep the upper-most data only." I need to make the cells one with all my information. Is this possible? Not knowing what you want to do, let me make a suggestion. Put all your data into the first cell and leave the second cell empty. Select both cells. Click on Format - Cells - Alignment tab. In the "Horizontal" b...

Excel Problem
I have a 23.8 meg excel 2000 spreadsheet set for manual calculation saved to my local hard drive. Every time I try to open it, it takes forver and sometimes never opens but I do not get any error messages, let me just tell you that I am running a P4, 1 GB memory, Office 2K with SP3, and nothing else running when I try to open it. As I said it is set for manual calculation, and it is cleared to not auto calculate when opening or closing. Any idea's as to why this is happening? -- Todd I don't know why you're having this problem but I would like to point something out for w...

I've built an Excel 2002 form that I want our internal customers to access from our intranet, and use. Once completed, they will send it to us as an e-mail attachment. I'd like to be able to open it, and somehow download the data from the form into an MS Access 2002 database I've built (so that we don't have to rekey it into the database). Is this possible or even feasible? Any and all help is appreciated. Thanks. :D --------- Message sent via www.excelforums.com Hi in Access check 'File - Import External data' -- Regards Frank Kabel Frankfurt, Germany "...

SOAP Exception
Hi, In my C# code, I have defined a Map object which is sent as part of a request to a web service, here's an extract of the part of the code: - Map map = new Map(); map.item = new mapItem[4] { new mapItem(), new mapItem(), new mapItem(), new mapItem() }; ................. But when I submit the request containing the Map object, the compiler throws an error with the following message:- org.xml.sax.SAXException: No deserializer for {http://xml.apache.org/ xml-soap}...

Using Visio HTML output within frames
Hi, I want to include visio HTML output in a frame of another html file. Unfortunately it is not working. I understood the problem is in vml_*.htm files. It is due to, the target arrtibute(pointing to _parent) in v:shapetype tag and href attribute (pointing to #) in v:shape tag. These attributes should point to "_top" and "<target-html-file>#" respectively inorder to work. I want to change these options while saving .vsd as web page? I would appreciate if you can help me in this regard. <v:shapetype id="VISSHAPE" target="_parent" coor...

Newbie Question: Using Web Services
Hello All. I've been trying to implement Infopath with CRM with no success. I've tried the example, http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnmbscrm1_2/html/mbs_crminfopathcrmintegration.asp, to no avail. I have followed the example to the letter, but find the information about publishing the Web Service to the server to be somewhat lacking. Admittedly, I am not a programmer, and I am continually running into an error in line 54: xmlDoc.LoadXml(objQuery.ExecuteQuery(objBizUser.WhoAmI(), strAllAccountsFetchXML)); Has anyone else ...

HELP! remote data not accessible msg
Hello, I have a use who is currently using a Bloomberg DDE add- in. Whenever he attempts to activate the add-in to retreive remote data, the system hangs. If I go to task manager, I then see a message stating "Remote data not accessible. To access this data Excel needs to open another program.... I have searched the knowledge base and didn't find much help. Does anyone have any ideas? I am desperate!!!! We are currently using Excel 2003 in XP Professional. TIA, Ramissah ...

Border problems
Not sure why all of a sudden all my borders in my tables created with Publisher can only be white. No other color will show when selected. Opening a pub file done on another computer where the borders show color, shows white only. I have attempted to do a repair on publisher, which gave no help. Have attempted to uninstalled and reinstall Publisher without clearing the problem. Anyone have any ideas or suggestions? Look in the Accessibility Options in the control panel, display tab, disable "use high contrast." If that doesn't solve the issue, read the third FAQ here....

Outlook 2002 XP SP 3 Archive problem
Hi there I am trying to get my newly configured outlook to archive my folders. I have ust configured it to download two POP email accounts and that works fine. I have set up a set of folders below my outlook (Personal Folders / pst) inbox node and placed the relevant emails in the corresponding folders. I have gone into tools -> options -> other -> and pressed the "AutoArchive..." button -> and set the "clean out folders older than" selection boxes to "3" and "Months" and pressed the "apply these setting to all folders" button. I ...

How do I copy data in single cell format to a merged cell format
I need to copy a list of data in single cell format into a spreadsheet where those same columns are merged cells (with four cells merged into one). I have several hundred lines of data so some way of copy & paste (other than each cell individually) would be appreciated. Thanks Paul Hi Paul What I would do is copy format of sheet with merge cells to blank sheet then copy single cell data to sheet with merge cells select single cell to paste you lose formatting so after just copy format from blank sheet created earlier I find this easiest way Hope this helps Tina "Paul" w...