I have a document which is full of HTML code. Please find attached. I have copied the information into an excel document and I need to find Twitter addresses i.e @test123 and place these in a separate column.
If i make a search there 190 of these addresses.
I have a table (called 'tblFuels' on worksheet"Fuel Data") which contains Fuel Names in column A, I have named the range 'FuelNames', there are several properties for each fuel in columns B to V.
On a separate sheet i have a dropdown box which is populated by 'FuelNames'. When a fuel is selected from this dropdown list, and a button pressed, i would like the corresponding row to be copied and pasted in to a third sheet for use in calculations etc.
The internet has given me several ways of doing something like this and I've given this one a crack, it just doesn't work.
Sub Find_CopyRows()
For i = 4 To Worksheets("Fuel Data").Range("A64000").End(xlUp).Row
'("InputFuelSel") is the 'range name' of one cell which contains a dropdown list of all the fuels found in column A
[Code] ......
Fuels Spreadsheet.xlsm
I am trying to figure out a formula that performs the following function:
I want it to find the date in column A in Sheet2 in column A in Sheet1 and return the highest value of column D(sheet1) for that same date. Is there a formula that can solve me that?
I attach an example of my worksheet.
I am trying to work out some code that will allow me to search worksheet2 to find the last filled cell in column A.
I would then like to copy the contents of the cell.
Then past this data into the first empty cell in a column in worksheet2
I have a workbook with four tabs or four sheets.
Tabs 1, 2 and 3 have a column of data (Column A) on each sheet. They all start from the same cell. Each cell of data are just numbers. The column (an array) of data will not have blanks between.
But, they are not the same number of data. They vary.
Meaning, Tab 1 may have 15 numbers (A1 to A15), Tab 2 may have 20 numbers (A1 to A20) and Tab 3 could have 5 numbers (A1 to A5). Each iteration of the workbook may have different number of data in the A column on these tabs.
Now on Tab 4, I want combine the data from all three tabs into one column (in column A).
So, Tab 4 has a column A with data from Tab 1 copy and pasted to (A1 to A15) as values, then (A16 to A35) have Tab 2 data copy pasted as values and (A36 to A40) have Tab 3 data copy pasted as values.
Basically, the macro on Tab 4 has to count the number of rows on each Tab that are populated with data values and figure out to copy all the data on Column A from each three tabs and paste the data value into the Tab 4 in one column of data (in values).
I'm working with a pre-existing list of vendors and currently the addresses for each are in one column. I would like to separate by city, state, zip. I was thinking of text to columns but since the addresses are not all aligned that won't work.
View 9 Replies View RelatedEssentially, I have 2 Sheets:
"SheetWSS" = data to be copied
"SheetWSD" = destination of copied data.
Below is a sample of the data in "SheetWSS"
----------A------------------------B-------------------------C
1------Trade ID -------Description---------System no.
2--------579----------------Loan ---------------------- 7
3--------580----------------Deposit---------------------22
4--------702----------------Deposit--------------------- 11
5--------703----------------Loan ----------------------- 58
6--------732 ---------------Loan------------------------66
7--------733----------------Deposit-------------------- 99
etc...(no more than 10000)
Now, an explanation of the data:
1) I work for a small bank (CORP) that takes deposits and gives loan.
2) CORP books these trades using only system no. 7 and 11; other system nos. belong to customers.
3) When a single trade is booked, the 2 sides of the transaction is recorded (by Trade IDs) . E.g rows 2 and 3 relates to one trade. So if CORP loans money with system no.7 to CUSTB, who uses system no.22, it shows for CORP a loan and for CUSTB, a deposit.
4) Everyday, the data in "SheetWSS" is updated with a different number of rows from previous, but the number of columns remain the same.
So, here's what i'ld like the macro to do:
1) Go into Column C, find system no.7 and 11.
2) For each 7 and 11 found, Copy the next row . So if for e.g, system no.11 was found in C4, i want the ENTIRE ROW 5 to be copied.
3) Paste the entire next row in "SheetWSD" until we have a list of all opposing sides of the same transactions initiated by CORP.
I have a list of about 250-500 email addresses, in which there are a few duplicate email addresses as well. How do I find the Duplicate email addresses and get a consolidated list without any duplicate entries in it ?
View 4 Replies View RelatedI need to find the last instance of "IO" in column E and copy cells in columns B to E for the row below to another tab called "OP" cell O9.
I need the VBA code for Excel 2003.
how to work macros or VBA
I have a workbook with multiple sheets named by month and year that I use to keep track of loans I work with at a bank. In these sheets I have info such as:
Column B = due date
Column D = loan# A
Column E = loan# B
Column F = status
Column H = followup needed
(Columns A,C, and G aren't important for the current need)
What I am trying to do is create a main sheet (TRACKER) that all I will have to do is press a macro button and it will pull info for each loan that is in a pending status.
I need a macro that will search column F (Status) on all sheets and find each instance of "Pending" and once that is done, copy rows B,D,E, and H in each "Pending" instance and copy them to their designated area on my TRACKER sheet. After that is done I need it to continue to the next "pending" instance and do the same on the next available line on my TRACKER sheet.
The overall goal would be that everyday I can press the macro and it will repopulate the sheet with the current pending items (as each day I will change pending status' to complete and no longer need to track it the next day).
I already have the tracker sheet set up and ready to go with the spaces as follows:
Column G&H = Merged cells where due date will need to go
Column I&J = Merged cells where loan# A will need to go
Column K&L = Merged cells where loan# B will need to go
Column M thru S = Merged cells where followup needed will need to go
I have data that is on a separate .txt file (the source file) that resembles this;
A
B
C
D
E
F
1
Case
District
Pct
Division
Level1
Level2
2
305035
0
20
72
[Code] ..........
Using VBA from within the destination workbook, I would like to use an input box that allows the user to request/specify what numerical number (0 through 55) in Column B of the txt file (the source file) will be used to determine what data to copy to the destination file. The cells to copy to the destination file would be from Column A through Column G of the source file onto Sheet1, E2 of the destination file.
Column B of the Source file will only contain numbers. However there will be numerous identical numbers in Column B. All of which will need to be copied onto the destination workbook.
From the sample above, if the user were to enter '15' in the input box, the desired result on the destination sheet beginning at E2 would look like this;
E
F
G
H
I
J
1
Case
District
Pct
Division
Level1
Level2
2
502046
15
1
3
[Code] ............
Is there a way to separate the suffix from various addresses i.e
A1= FOSTER AVE
A2=WISE
A3=EL DORADO ST
A4=OLD HIGHWAY 221
Some of the addresses are up to 5 words and numbers, some are missing the suffix all together.
I need to return a value from a cell in Column H, but with a row number that varies with each entry.
I repeat this formula every 5 rows or so. But the cells referenced on another spreadsheet are only one row apart. Because of this discrepance, when I copy my highly developed cluster of formulas, they only reference every 5th entry on the other sheet. What I've been doing is manually altering long, complicated formulas by hand. I can't do a replace function because every once in awhile it'll find an extra instance of the digit I'm replacing and mess up parts of the formula I don't want changed. I could avoid this tedious, time-consuming work-around if I could make a formula that would do something like this:
=Display Value from in column H: (row number per cell B1)
And cell B1 would include the number 3789.
So the returned result is the value from cell H:3789
Have an Excel 2010 spreadsheet with 902 email addresses in one column, and 927 email addresses in other column, sorted in alphabetical order.
Overview: Organization database has 902 Members (some have 2 email addresses subscribed to listserv) and Organizational Listserv which has all 927 (Difference is numbers are some members have 2 email addresses subscribed) BUT 902 of emails should be the same
Objective to compare Listserv Members 927 with Database Members 902. I am trying to isolate the email addresses which do NOT exist in Listserv Column, or who do not exist in Database Column for Audit purposes. All Members from the Database column should be subscribed or entered into the Listserv column.
If it's a perfect world the 902 email addresses will all be exactly the same as 902 out of the 927 Listserv email addresses.
Trying to highlight or sort and put identical email addresses on same line or using Conditionally formatting? Highlight Cell Rules/Duplicate cell rules Even when same email addresses are on the same row conditional formatting does not recognize them.
I have 2 spreadsheets. One is a promotion calendar that lists the dates that promotions on a certain product runs. The other is a shipment grid of shipments of that product to the customer.
I want VLookup to find the customer and the dates and then bring me back an asterisk in a separate column to show me that that certain week that product was delivered was a promotion week. The problem I have is using Vlookup to lookup 2 things at once (and if they match to the promotion calendar) and return me an asterisk.
Here is my formula now:
=IF(VLOOKUP(J2&" "&L2,'East Data'!M:AU,2,FALSE),"*",0)
J2 is the customer name
L2 is the week
"East Data" is the spreadsheet with all of the promotions and customers.
I'm back working on my estimate sheet again and hit another roadblock. I have a series of rows all separated by multiple spaces and would like to copy every one to the first empty column on a separate page sequentially until a certain condition is met (first time row starts with zero in column U, in this pic second row down would end routine).
The first column here is U on sheet "Partitions & Woodwork" so since this first row doesn't start with a zero, U10 - BC10 would need to be copied and pasted transposed into the first open column on sheet "Rebirth" (2nd pic below).
U V W X Y Z
It would be pasted transposed here from B2 downwards on sheet named "Rebirth". The next row that didn't start with zero would be pasted transposed starting at c2 and so on until the first time a row beginning at column U on sheet "Partitions & Woodwork" began with a zero (0).
The number of spaces between rows being copied varies on the partitions & woodwork sheet but the columns (U - BC) are a constant every time a row needs to be copied.
ID Name QTY Price
1 John 5 15
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125
1 Tim 7 26
4 Sue 10 100
I need macro to loop through all data and generate this table in another worksheet
1 John 5 15 Tim 7 26
2 Jim 6 20
3 Sue 10 200
4 Fred 12 125 Sue 10 100
Each data in a row in the new table will occupy a different cell.
Let's say I have a value in a cell that I'd like to post on my Twitter account, would that be doable?
View 3 Replies View RelatedI have an address list of 64,000 entries that I want to geo-code (the geo-coding isn't the problem...). A large % of addresses don't have street numbers and this just gives a general position based on the Post/ Zip code which isn't accurate enough - it needs to have an actual street number. I want to extract all of the addresses without a street number so I can cross check them with something else (not an Excel problem). Trouble is some of the street numbers are embedded in a string - might be a multiple NOT problem. Attached a sample file...
View 4 Replies View RelatedI want to create a macro to tell me how many twitter followers (and facebook if possible) a website has based on its web URl.
Websites with a twitter button contain the twitter handle in the source code:
eg.lifestylesports.com contains https:[url]......
I then need the macro to open the twitter link and give me the follower count which we can get from the user twitter page e.g
followers_count":14516,"
This then equals the twitter follower count of 14516
I want to do is add data into Sheet A each day then press a button which will copy the data in a range and paste it into Sheet B, but I want to create a list of all data so I need it to find the next empty cell and start the paste from there (if that makes sense).
I want it to create a data base on one sheet from a daily import, I have a code to copy one cell to next empty cell but dont know how to duplicate it to a range.
I hope I have given you enough info this is what I have done so far
Public Sub CopyData()
Dim ws As Worksheet, bi As Byte, vData(1 To 1)
Set ws = Sheets("A")
For bi = 1 To 1
vData(bi) = Application.Choose(bi, ws.Range("A1"))
[Code]....
= IF(AND(MATCH($B8, [AXS.xls]Sheet1!$E$1:$E$400,0) - 2 = $C8,
INDEX([AXS.xls]Sheet1!$B$1:$B$400, MATCH(CX$2, INDEX([AXS.xls]Sheet1!$B:$B, $C8 + 16):INDEX([AXS.xls]Sheet1!$B:$B, $C9), 0) ) ),
INDEX([AXS.xls]Sheet1!$A$1:$E$400, MATCH(CX$2, INDEX([AXS.xls]Sheet1!$B:$B, $C8 + 16):INDEX([AXS.xls]Sheet1!$B:$B, $C9), 0), 3), " ")
i have this formula, and i think it will work if i find a way to correct the blue areas. they are trying to define the match array. i tried using the address and cell functions with no luck. i just want the array to return something like B2:B15 (from the AXS sheet) so the match function works. in the current sheet, C8 and C9 have values that refer to rows in the AXS sheet
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count > 1 Then Exit Sub
If Target.Column = 1 Then
If Target = "" Then
Cells(Target.Row, 3).ClearContents
Else: If Target.Value = "test" Then Cells(Target.Row, 3) = my.Email.co.uk
End If
End If
Application.EnableEvents = True
End Sub
Basically, my target column is column A, i have 200 employee numbers, now we have to find the email addresses manually which is useless so i want to spend the time writing some code like:
If the target column is 123456 then the offset column D is email address
If the target column is 654321 then the offset column D value is email address.
I have 200 statements like this ill need to add unless anyone has any suggestions, perhaps select case structure?? I dont really know where to start an so i await your replies.
I have a worksheet, "District", that has names of team members from A2:A (The number of team members will vary, so I would need the macro to stop when the list ends). I need these to be distributed to column A on another sheet, "Input", from A11 down until it hits a row that has a blank cell in column F. I've tried a couple of things, but just can't seem to get it to work.
View 14 Replies View RelatedI would like to implement specific cell ranges from two specific worksheets each within 33 workbooks (which all have several tabs) into a summary page in a separate workbook.
The cell ranges are going across my spreadsheet in rows and I would like for them to transpose into a columns depending on the data which I have separated by catergory on the summary page. They are all on the same location in each workbook which is separated by country. The cell ranges are E26:P37 and I would like to transpose them and have them put below eachother without overwriting for my format on the summary page, how I can put this together in a macro?
I have 10 very large workbooks that are all setup in the same format. In column Z is a numerical value from 1 to 83. I have been trying to filter the sheet and then copy one at a time from 1 to 83 but that takes a LONG time especially when there is 10 workbooks to do.
Is there anyway I can run a function or macro or something that would just automatically look down the column Z and put each row into a it's own workbooks?
I have attached a sample of what the workbooks look like right now.
Sample123.xlsx
I am trying to create a macro that copies the values from A2 to the last row of data on sheet1 and past them to cell B2 on sheet2
View 2 Replies View RelatedI want to copy and paste from one sheet to another based on column a using a macro copy button.
E.g. if column a value = apple then copy that row into the apple sheet.
I want to write macro for below requirement.
I have 10 file in a folder and in that there is address, pone and other detail of employees in these sheets it will be static from D3 - D13 rows. Which is of below format:
Original format
Name
Address1
Address2
Phone
Sex etc..
I want to the above data to populate from these 10 sheets to one sheet (new workbook) as columns as below format :
Sl.#, Name, Address1, Address2, Phone, Sex etc...
I want to write an macro to achieve this. Please let me know your views to achieve this functionality.
I need to make a Workbook that has hyperlinks in the first sheet that links to a cell in a second sheet and then back again. The only way I have been able to do this is manually create each link in each sheet. I am hoping that this process can be automated as they can not be 'filled' as far as I can see. find attached an example of what I am trying to achieve.
View 2 Replies View Related