I have a list of UK Post Codes that I wish to filter but because of the format I'm getting problems.
The reason is that I'm only interested in the first part of the post code.
Example: Using IP7 6NH and IP32 8LR. If I tried to filter those using greater than then Excel would treat IP7 6NH as larger then IP32 8LR because 7 is larger then 3.
The post codes are all imported from another piece of software and are always in the format of 3 or 4 chracters, then a space and then 3 more chracters. I wish to create another column which only has the first part in it. So I wish to crop IP7 6NH down to just IP7 and IP32 8LR down to just IP32.
Then I want to make a further column which looks at the value in the first new column and returns "In" or "Out" based on some conditions.
These are the conditions. If the cell begins with CO or NR or CB or CM or PE, regardless of the rest of the value, then "Out" should be returned. If the value is between IP1 and IP6 inclusive then "Yes" should be returned.
If the value equals IP7 then "No" should be returned
If the value is between IP8 and IP17 inclusive then "Yes should be returned.
If the value is between IP18 and IP29 then "No" should be returned.
If the value equals IP30 then "Yes" should be returned.
If the value is bewteen IP31 and IP33 inclusive then "No should be returned"
I'm think that it's quite complex and maybe the way to go would be to have two other columns, one containing the "In" values and the other containing the "Out" values, that a formula can refer to when making it's yes or no choice but I don't know how to do that.
I have been able to produce a column that gives me key date information. I might have many empty cells in that column before it hits another date. I need to get this data over to another sheet and place it in a row.
I am trying to get it to look like this:
B2(value), C2(value),D2(blank),E2(Value),F2(Value),G2(blank) etc depending on how many more elements are found in the sheet.
I am able to find topics that allow me to copy a range of data but not one that will allow me to copy a range then skip a cell.
I receive a workbook on a monthly basis and it arrives in csv format. I need to format it and split it into several pages prior to sending to managers. I'm comfortable with the vba for formatting and splitting it into several pages but I have a problem with some cells that always have data in them in the same (incorrect) fashion.
how to go about fixing these cells and then I can get on with writing that into the macro.
Currently looks like:
A || B || C No requested || No Supplied || Percentage 4 || 4100% 12 || 875% 6 || 350%
Should look like:
A || B || C No requested || No Supplied || Percentage 4 || 4 || 100% 12 || 8 || 75% 6 || 3 || 50%
I was thinking of some kind of Left() formula to extract the leftmost character and place it into it's new cell BUT realise that sometimes I might need to extract 2 (or perhaps even more) characters.
I'm trying to extract information from one specific cell, and place it into another. This other thread kind of does what I'm looking for. I wish to extract from the following; "20/04/2009 5:42:51 AM". Placing the date in one cell, and the time in another. All spacing within the quotation marks is respective of the cell. Obviously dates and times would vary.
I am trying to extract only partial information from a cell that contains mutliple entries without seperator. Ie I want to get 3rd reference from cell containing abcde(I want to extract onto another sheet the "c" and only the "c")
I'm looking to find a way to extract an email address an a reference number from an email receipt that i have extracted to excel. The problem is when I paste the information its pastes onto multiple rows and not a single row.
I'm using this formula to extract the email address and can do something else to get the ref number out however the results are on multiple lines which is not good to me.
I have attached a document to show you how the data extractS on (EXAMPLE TAB) The information I want to extract is the stuff in Red. I then want the results to look like the Results Tab in the example.
I had a situation where address information for a set of contacts was either on 2 or 3 rows depending on whether there was a Suite/Apartment number....meaning City, ST Zip might be on the 2nd or the 3rd row.
Since I couldn't think of another way, I opted to append it all together to start, meaning now my addresses would be, for example: 100 Main Street #200 Annoyance, SD 57000
Of course the ideal would be to have street address in one column, city st zip in the next (or next 3) column
My thought was there should be a way to find the first space prior to the comma, and split there....but it's eluding me
I have some data that I have created a report from. However it doesn't show a "true" picture for the stat we want. I have a pivot table set up with data showing the hours worked for an individual. What I am wanting to do would be to cross reference the data but not sure how to compare data. I have provided some make shift data to see if it is even feasible. The end goal is to get a true picture of P.P.H The data we are able to get is in a horrible format. I would eventually like to automate this in VBA but will work on the later. I thought about doing a vlookup but the is no real matching data for all employees. Some of the names are wrong in one system and there is no matching emp ID.
I am doing an export of info from one system and running an excel spreadsheet to manipulate the data. The problem is..That the exported data sometimes will export info into combined cells. Look at attachment
This is what I need...
I need the ability to search all the info on the POC tab and pull it into the info tab, by searching on a system and returning the POC info..
I have tried the vslookup formula and it will return the first row not the second row which is where the info is that I need (shown Highlighted) =VLOOKUP(A4,POC!A2:b50,2,FALSE)
I am trying to write a formula which will extract information from a given set of cells. The information format is
What I need to do is pull out the information which precedes the second. so in the above the answer would be XXXX-YYYY. I was thinking of using a mid or left, right formula till I discovered that the # of characters for X, Y and Z can be any length ie they could be
X-YYYY-ZZZZ XXXX-Y- XX-YYYY-ZZZZZ
So the formula needs to find the second dash (-) and extract information prior to it.
I use a spreadsheet (FY-2012) to track approx 700 work requests. I enter the work request number (name of the spreadsheet) in column A, which is hyperlinked to the actual work request spreadsheet. I would like to automatically fill in this cell with the information entered in cell J42 of the work request entered in column A. Cell A5 of the tracker "FY-2012" indicates Work Request "BB-12003". Cell D5 of the tracker "FY-2012" indicates the completion date. I would like to extract the completion date entered in cell "J42" of Work Request "BB-12003" and have it automatically fill in Cell D5 of the tracker "FY-2012".
I have inserted the sheet. As you can see in Sheet 1 I have the Store, Address, Town etc, I would like a drop down box in D13 ( Store Name ) so when I select a store it will automatically fill all the info into D15:D19 Address, Town etc. The info will be pulled from Sheet 2.
I have a string of information in an Excel download from PayPal that I need to upload into QB; but, the information needs to be in a different format. The string from the download is:
Property Address - 1116 South PaxonBorrower " Testa and Son Contractors,LLCLoan # - 1402088Acct Executive " Brandon Tully
But, I need it to be:
1402040;TPAPR;141 North Clinton Street, East Orange, NJ 07017;Leslie P Carter
Is there a formula that I can use to extract specific information from the original string into columns so I can then concatenate the columns into the information I need to upload? Or is there a better way to do this? The Property Address and Borrower Name are not static sizes so I can't use a formula that counts spaces.
My problem is I cannot copy from a Form Control drop down box, and I need to do this for ~10 drop down boxes in about 200 xls files. The files follow a template so the drop down button Inputs are uniform. Here is some more detail:
I’m building an Access Database so that our data (currently in many, many Excel Spreadsheets) can be efficiently queried. The old system was that when asset data was collected, the data would be entered into an Excel spreadsheet, using a Template. So for example:
Name Main Street Pipe High Street Pipe
Ref No. 12345 12346
Installation date 22/08/13 19/07/12
X Coordinate 55667 99212
Y Coordinate 77889 12364
We have hundreds of these files, but I managed to get all the files in one folder and Paste and Transpose the data from the relevant Cell Ranges in each file (using a VB sub) into ONE summary sheet. Success; or so I thought.
Unfortunately for one generation of the template (people changed them slightly every year), the template had people enter information into Drop Down Menus. Not a problem when they’re associated with a cell, but these were free floating Form Controls.. so you can’t actually click and copy from them, or call them in VB as you would a specific cell.. afaik.
So my issue is how can I extract the data from those Form Buttons? I’ll try and provide as much info as I can:
Firstly I tried to find some sort of identifier for each Form Button. The dropdowns are called Combo Boxes, (Developer tab > Insert > Combo Box (Form Control) ), and when you select them (by right clicking) and hit the View Code button in Developer Tab > Controls the following shows up in VB.
I’m guessing that the number is related to the button so that’s one useful thing, an identifier of sorts. There’s no actual code there though really from what I can see.. My problem is I have no idea how to extract from DropDown44!
The range of values in the drop down comes from a separate sheet where there’s just a list of the values. There aren’t any cells linked to the drop downs though. If I enter a cell in (Right Click) Format Control > Cell link: then it prints the number (e.g. 4) of the value in the range. So if the drop down options are “Monday”, “Tuesday”, “Wednesday”, “Thursday” and “Thursday” is selected, the linked cell says: “4”.
They didn’t link a cell, then I could call that cell in a VB sub and the job would be done. Unfortunately they didn’t, so I tried Recording a Macro and selecting the drop down form control.. That didn’t really work, except if I right clicked it, I got some more info from the macro VB:
ActiveSheet.Shapes.Range(Array("Drop Down 19")).Select
So is my drop down an Array? My understanding of an Array is that it’s a list of arguments.. all I get there is the name of the button. I am not experienced in VB so this is as far as I’ve got. Perhaps I can get a sub to select the button using the above line, but I need the info from it, i.e. which value is currently selected in the Drop Down.
(Using Excel 2010 but the .xls files are from ~2005)
i have a workbook blank template for my colleagues to fill in, they fill in this blank template and save it under the serial number of the item they are creating. However a lot of errors exist when they input the serial number inside the workbook where the serial number is a duplicate of a former serial number.
Anyhow i was wondering is there any way when they input the serial number into the worksheet that i could get it to save in a separate workbook and then have conditional formatting to check those serial numbers in the workbook to see if they match?
I'm trying to convert various codes from one column of an excel spreadsheet into different codes in another column. I was able to accomplish this with "If" statements, however I'm only able to string together seven of these statements in one command. Is there a better way to add formulas for more than seven conversions? Below is a copy of what I've done so far with the seven converts:
I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.
In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.
I am struggling to find a macro which can look at a name in column 'BT' and search it in the address book of Outlook to then place the email address of that person in column 'ED'
There are 35,000+ people in the address book and there may be over 5 email addresses for one name, so is there any way a message can appear for the user to select which email address is correct if there is more than 1 contact for that name?
Assume the following list of addresses are all in separate cells of a single column (A1-A4). I just need the formula to extract the street addresses, and then a separate formula to extract the zip codes.
5430-44 PASCHALL AVENUE PHILADELPHIA, PA 19143 OPA/BRT#: 884350845
4010 MARPLE STREET PHILADELPHIA, PA 19136 OPA/BRT#: 651087200
2618 SOUTH HOWARD STREET PHILADELPHIA, PA 19148 OPA/BRT#: 391251216
5737 WOODCREST AVENUE PHILADELPHIA, PA 19131 OPA/BRT#: 522155600