I got 2 sheets. (1 called "Revenue", the other "101") in the sheet revenue are department codes (variable 100 to 999) they are situated in row "B". the corresponding data in row "D".
this data needs to be transported to the sheet "101"
in the sheet "101" are put the numbers 100 to 999 in row "A"
in row "B" we need the data from the revenue sheet that is corresponding with the department code.
I've written a procedure that loads a list of country selections in a dropdown (see attached/vb editor/ThisWorkbook).
The problem is the valid values for this column need to be just the two digit country codes, not the country code +dash+country name. The reason the dropdown selections include both the two digit country code and the country name is because most users will not know the two digit codes. Presenting the selections in this fashion will make t much easier for the user to make the proper selection.
So here is what I need: When the user makes a selection from the dropdown list, the resulting value needs to be the two digit country code only. Is there a way to incorporate this formula .... =MID(B2|1|2) ... in the vba procedure which runs after the user makes a selection? Or, is there another way to accomplish this?
Example_COO.xlsm‎
I have 4 worksheets in a workbook: “choose security”, “download”, “MC” and “Lending”,
In the choose security” worksheet in CELL A2 is an HEX code, i.e. stock code.
2. In MC worksheet is raw data of all holdings related to various p’folios and securities, the information we need from this worksheet is,
a) p’folios, which are in column A,
b) Holdings which is Col B
c) Security code i.e. stock code which is in Col D
I need a macro or a formula to look at what security code is in “choose security” worksheet CELL A2, then look for this security in worksheet “MC” Col D, once matched, copy the data for this security to worksheet “download” and paste in CELL A3, I only need p’folios and holdings to be copied to “download” so data in Col A and B.
Once data has been copied to worksheet “download”, I then need another macro or formula to repeat step 2, but this time the macro will be looking at worksheet “Lending”,
a) P’folios, which are in Col B
b) Security code is in Col D
c) Holdings is in Col H
Once the macro or formula has found a match of security code, it will then copy the data from Col B and H to download worksheet CELL E3.
I am unable to install a disk catalog and have been trying with no success to try and get something in excel that will look at a directory and display the contents of that folder with the file attributes etc
View 2 Replies View RelatedVB code to carry out the following task?
Look at name in cell BC2, check the name in cell BA2 and if is the same delete the name in BA2 and BC2.
If is different then leave both cells as they are and move onto checking BC3 against BA3
carry this loop thru BC2 : BC40
I have data in 2 workbooks. Book1 - Sheet1 - Col A and Book2 - Sheet2 - Col A. I want to compare Book2 with Book1 and list all values in Book2 that do not find a match in Book1. I want to run a macro for this
View 7 Replies View Related1) Background Info
We are trying to summarise some data that has been exported from an ancient database into a poorly delimited csv file. My colleague has imported the csv file into Excel ( attached), and we are trying to work out what to do next.
The dataset contains approx 300 records - each record being called a "sample" and having a unique sample number. NB: I have had to attach a cut-down version with only 3 samples, due to file-size - but it will hopefully give an idea.
Each sample contains 2 types of information that we are interested in (and a lot of irrelevant data besides). The relevant bits are:
- predicted occurrence of various species (given as a percentage for each species)
- observed occurrence of various species (given as positive/negative for each species, where positive is indicated by an asterisk)
2) What We're Trying To Do
We are trying to compare this dataset with a master-list of 80 species names. For each species on the master-list, we want to:
a) check whether it has a "predicted occurrence" value in each of the 300 samples (and if so, record the value in a summary sheet)
b) check whether it was observed in each of the 300 samples (and if so, record this in the summary sheet)
3) The Problem
Unfortunately, the dataset has imported into Excel as a gigantic list: 48000 rows (including loads of blanks) and only 3 columns across. To find the relevant data, it's necessary to:
a) open the attached workbook, and go to the worksheet named "Data"
b) Scroll through the rows, looking for the string "RIVPA" in column A. This tells you where each new sample begins. (The sampleID is stored in the same row as this, in column C. It is mixed up with a load of text, which we will need to separate out at some point, but that's a secondary consideration at the moment.)
c) Scroll down further until you find the text string "Predi" in column A. This indicates the beginning of the data we're interested in, for each sample (i.e. for sample 1, I'm talking about row 58). Count down a further 2 blank rows, and then you find the data itself:
- Column A contains the observed occurrence (a positive result is indicated by an asterisk)
- Column B contains the predicted occurrence
- Column C contains the species name
My colleague was trying to build a summary table, in the worksheet named "Summary". He was using the LOOKUP function to extract the data, but because there are so many samples, it's beyond unwieldy. He asked me if I could write a macro do do the job, and this is where I'm stuck.
How could I set up a macro that can identify where one sample starts and finishes, and where the data is within each sample? What is the most sensible shape for such a macro? If I could get the overarching logic worked out, I could make a start on writing the individual bits of code, but at present I just can't figure out how to begin.
To make things more difficult, the samples are not all the same length, and do not all contain the same list of species as each other. (However, at least there should be no species in the samples that are not in the master-list). The first sample begins on row 5, the second sample begins on row 173, the third on row 340, and so on.
the listbox will show week 1 to week 52 and let me choose one of the week.
Private Sub OKButton_Click()
If ListBox1.Text = "Week 26" Then Call week26
If ListBox1.Text = "Week 27" Then Call week27
Unload Me
Sheet7.Activate
End Sub
Sub week26()
With Sheet7
.AutoFilterMode = False
. Range("A1:I1"). AutoFilter.............
Since there are 52 weeks in a year, i have to type the codes for 52 times for different weeks. i don't need to type all these codes 52 times, instead one time with a loop or something else.
I'm trying to write some VBA code to do the following (don't think its possible using formulas only). In column A i have a list of codes (A1:A32) in column B i have a value (between 1 and 32 in this case, but feasibly could be any integer) I am trying to achive an output in column C which would take the code from col A and then "count up to" the value in col B;- for example "AAA" in col A, and "4" in column B would return AAA1, AAA2, AAA3, AAA4 in column C. For the next code say BBB and corresponding value, would start its sequence in the next empty cell below AAA4 in col C.
View 9 Replies View RelatedI am trying to compare multiple column in a worksheet to find
common component in all the columns and what is unique to a particular
column only. And list the results/finding in adj column. What i am
trying to accomplish is something as below.
BEFORE
Sheet1 Sheet2 Sheet3
Column2 Column2 Column2
02-1234-12 07-1234-12 02-1234-12
04-1234-12 03-1234-12 02-1234-12
05-1234-12 02-1234-12 06-1234-34
AFTER
Common to all Unique to sheet1 Unique to Sheet2
02-1234-12 05-1234-12 07-1234-12
I have attached a sheet that has part of a data list, sheet called (Full Data) what i am trying to do is seperate the data into event locations into individual sheets.
The data ref will be column F which is the different event locations.
I thought the best way to try and do this was to create an if/ match formula using the sheet tab names as the if match, with the event locations in column F.
I have added two sheet tabs so you can see that i require the data for (Ain) to be put into the Ain sheet.
The data list in Full Data sheet will be continuously updated so i will need the range to be around 10,000 entries.
I am creating a billing sheet that has a column where charges are coded (to determine what they are for) and I want a "Grand Total" for everything followed by a breakdown "Total" for things with the same code. Is there a way to set SUM code to grab all numbers next to codes?? (EX. Column A is Billing Code, column B is Cost in $$; I want a total at the bottom that takes the different amounts in column B based on the various information/codes from column A).
View 2 Replies View RelatedI've created a Excel spreadsheet. In the Spreadsheet I've got one worksheet which has columns and list of codes.
I would like my other worksheet to look through find any products I've ordered and list them with their codes and quantities, creating a summary of just what I want so that I can e-mail just the important bits.
I have a large sheet of data I'm trying to sort through. Each row of data has a column that contains a long string description. Each of these strings contains a three letter codes in all caps that I want to be able to pull out. I have a seperate sheet that has all of the possible three letter codes in one column. Is there a formula that can compare each string of text to this list of three letter codes and if it find a match put that code in a column on the original sheet?
Doc Number
String
Code
33333
This is an example string of text. An example three letter code would be HCB. The location of the code within the string varies.
HCB
33332
This is an example string of text. An example three letter code would be CDQ. The location of the code within the string varies.
CDQ
33331
This is an example string of text. An example three letter code would be RCA. The location of the code within the string varies.
RCA
Codes
DCP
HCB
RCA
CDQ
LCP
DCA
SCR
I have a sheet with names and subdividers (Ent, Ver) on the columns, and dates on the rows. I am trying to make a form update the sheet when the name, ent or ver, and date match. I have attached a copy of my sheet to help explain that. I don't know much vba, but I can make a userform just fine. Trouble with most places is that the userforms only update based on empty cells, not based on more complex criteria.
View 7 Replies View RelatedI am using Excel 2011 on a mac. I have a selection of data that lists a repeating code (say group 1, group 2, group 3 etc)and a value (-1,-2.3,3,6 etc) and need to find a quick way group together all the codes and next to that a value that counts how many values who share the same code hold a value greater than -1? I can do this myself in a pivot chat with count totals but my issue is how I can show the number of codes greater than -1.
Attached is an example of the data I want to sort.
Example of data that needs sorting.xlsx
I call prospects to generate business. I want to respect their time by calling before 8:00 PM so I need to know their time zone. I have a column in Excel that lists their area code, but I don't know their state.
I have tried everything imaginable to duplicate the results of the previous post to no avail. I'm using Excel 2011 on a Mac.
I'm using =VLOOKUP(B2,$I$2:$J$364,10) for my formula, where
B2 is the area code $I$2:$J$364 is the range where the area code/time zone data lives (I = area codes, J = Time Zone) 10 is column J, i.e. the Time Zone column that "feeds" the answer.
I've attached a screen shot to show what I'm doing.Screenshot at Feb 05 15-33-01.jpg
I have data on Sheet2 and would like to only copy all rows associated with column D2 to the Sheet3. For example Sheet2 has two different values Voice or Data I would like to copy all rows associated with column D that contains voice to Sheet3.
View 4 Replies View RelatedI have made a spreadhseet that track what games an official referees (sheet 1), what games they are touch judge 1 for (Sheet 2) and what games they are touch judge 2 for (sheet 3).
Sheet 4 then adds all these up for me and tells me as an good approximate estimate how much each official should get paid in way of expenses.
Simple enough so far.
I have a list of 3 letter codes on sheet 4 for each official that I put in the matrix of games. It's using these 3 letter codes that it adds it all up. This works fine
How can I get it to tell me if any 3 letter codes entered into the matrix (Sheet 1!D4:CD82) Do not match my list (Sheet 4!A5:A71). (an error detection system if you like).
To make it more interesting I would like it to ignore blank cells (games not played yet).
I'm not bad on excel formulas, but I can't get my head around how to do this.
In 1 cell i need a forumla to give me a starting number and take one away each time a code or codes are dislayed in a cell range. Something like this
A1 = 23 days or any number days i needed
Cell range B1:C52
every time a selected 1 or 2 letter code appears in the cell range i want A1 to subtract 1 day from the starting number, i would need it to subtract half a day if one code appears ie HD, the codes may be P, OT, HD
I've been trying to create a formula that will take data with a specific status and put those in a list on a summary worksheet.
On my data worksheet I have two columns a category/ name column and a risk/status column, on my summary page I want to generate a list solely made up of names that have a specific status.
I've attached a sample.
I have a data validation list where more than one value should show the same list. Instead of make multiple range, I want to make it so if any of the values are in a cell the data validation list will show the list that goes with those values.
View 4 Replies View RelatedI'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:
=IF(ISNUMBER(SEARCH("WARN",J2)),"Warning",IF(ISNUMBER(SEARCH("PSSNAP",N2)),"Sales",IF(ISNUMBER(SEARCH("WARN",L2)),"Warning",IF(ISNUMBER(SEARCH("2699",L2)),"Warning",IF(ISNUMBER(SEARCH("4004",L2)),"Warning",IF(ISNUMBER(SEARCH("2036",L2)),"Warning",""))))))
I want to add about 15 more codes to convert within this formula but it's maxed out in the format I'm using.
Suppose I have a list of data in which I want to retrieve or extract specific row from the list as per operator name & relative comments (Done or Not Done) from the given list as per the operator name, now what I want that my VBA code should be able to get the operator name & the relative comment data from the list according to the operator name in a new sheet. My data keeps changing so I need a code which can work on any size of the data. I need to retrieve only operator name & Comment column in a new sheet to run my Pivot table.
My Excel data is something like in the attachment & the result I want through VBA Code.
I am currently in the process of creating a manifest system.
I have two sheets I use (DELIVERY MANIFEST & COLLECTION MANIFEST)
What I would like to happen is, when I input information onto one fo these sheets and click the Macro button to send an email, that selected information gets automatically transferred onto the first blank row on another sheet (TRANSPORT SHEET)
The data I want to transfer is:
Manifest----LIST
J8 A1
F6 B1
G19 C1
J6 D1
H11 E1
C9:19 F1
E9:19 G1
J15 H1
For Column J on the spreadsheet I require the name of whoever input the data ( name of excel user)
COLLECTION MANIFEST.xlsm
DELIVERY MANIFEST.xlsm
TRANSPORT LIST.xlsm
I am using following code to apply data validation list. However this code gives error Intermittently. The error message is 'Application defined or Object defined error'. Also this code looks little cumber some. Will really apprciate if any one can put some ligh on why is this error appearing sometimes. Do I need to change something in the code or add few extra lines. I feel following points will be necessary when you check my code
1. The data of validation list is stored under the column heading 'Reported_By_List'
2. Validation is applied on the range under the column heading 'Reported Through'
3. Start and last Cell Address Of the cells of the column in which data of validation list is
stored are extracted into the variables 'StartCellAddress1' & 'LastCellAddress1 '
4. Start and last Cell Address Of the cells of the column on which data validation will be
applied are stored are extracted into the variables 'StartCellAddress2' &
'LastCellAddres2'
Sub Validation1()
Dim wsName As String
Dim RValue As String
Dim WorkBookName As String
Dim StartRow As Integer
Dim StartColumn As Integer
Dim LastRow As Integer
Dim CellAddress As String
Dim ColumnAddress As String
Application. ScreenUpdating = False
WorkBookName = ThisWorkbook.Name
wsName = ActiveSheet.Name...........
I am trying to compare two sets of data.
I have a set of All zip codes in 10 counties in one worksheet:
ex:
Column 1 / Column 2
30303 / Fulton
30307 / Fulton
30310 / DeKalb
30234 / Cobb
But this sheet has about 300 zip codes
Then in a second worksheet I have dollars by zip
30303 / $10,000
30456 / $56,000
I'd like to make a third column in this sheet that fills in what county. Does anyone know how to search sheet one for the appropriate zip, then stick the word beside that zip into the column 3 secion of the dollars by zip code?
I'd rather not have to search every zip code in the dollars section one at a time. Not all the zip codes in the state are represented in the second sheet, so it's not quite as simple as just lining them up.
Sir Vog-II/Jindon
I have written mentioned below codes to copy data with certain craiteria can you please see them if these codes need to be improved or concise.
HTML Sub ayazgreat()
Dim Header
Dim Header2
Dim LastRow As Long
Dim Rng As Range
Header = Array("Region", "Item Issued", "Invoice Date", "Cheque #", "Amount", "Chq Date")
Header2 = "Total"
With Application
.ScreenUpdating = False
.DisplayAlerts = False
Sheets("Payment").Copy
ActiveSheet.Shapes("Button 1").Delete
ActiveSheet.Shapes("Button 2").Delete
Rows("1:2").Delete Shift:=xlUp
ActiveSheet.Range("C3:H3").Value = Header
ActiveSheet.Columns("E:H").AutoFit
Is there a way to compare specific data from three separate worksheets to see if they are equal?
I receive three daily reports that contain some of the same information that must match in order to correctly compile an Executive Report. Currently, I am doing this manually. Is there any way to automate the process to have a warning or something similar pop up if the numbers do not match?
I need to take the data out of one column from sheet A and compare it to the data on another column sheet B.
Real life example:
I have a very large list of contacts with multiple columns of data. On a separate spreadsheet, I have a list email addresses that I need to remove from the larger list of contacts. How can I compare them against one another to detect any matched email addresses for deletion?
List of contacts is appx 130mb
List of email addresses is appx 4mb