Find Cell Data In Cells Containing Extra Data
Feb 21, 2008
The 1st set is in a column. Nice & tidy all twelve characters in length & alpanumeric
zx0123123123
zz0123123124
zy0123123129
The second set is a mess, taken from a lotus notes inbox from the subject line off an email, eg. there Blah blah zz0123123124 etc etc. What I want to do is do a reconcilliation between the two with a lookup, but have no idea which route to go down to get round the messy data. I have been using =IF(ISNA(MATCH(A1,'[spreadsheet to lookup to.xls]sheet1'!$A:$A,0))," No match"," Match")
but here the data is clean. I have searched teh forum ;o) DaveH & found something about > Instr() but this can't be used in formulas..
View 5 Replies
ADVERTISEMENT
Jun 12, 2014
I need to create extra rows of duplicate data. The number of duplicate rows depends on the number of semi-colons in the 15th column (column O).
Sheet 1 is the original working file. Sheet 2 is the result of how the executed macro for the first 2 lines of data. The first line in sheet 1 has 7 semi-colons, so there should be 7 rows of duplicate data created in sheet 2. The second line in sheet 1 has 6 semi-colons, so 6 duplicate lines, etc.
View 3 Replies
View Related
Aug 17, 2009
I want find the data in some rows that same with one or more cell and automatically fill the data. And for more details, I have attached the examp file (Examp.xls).Antoni
View 3 Replies
View Related
Apr 28, 2014
I am trying to set up a spreadsheet that pulls all the data from an existing spreadsheet on a networked drive. The spreadsheet on the drive gets updated occasionally by about 10 different people. I am trying to set up a duplicate of it on a local machine so that a single extra column can be added that no one else can see.
So far I am able to get the data from the spreadsheet using data connections, but it won't copy across the comments or any of the colour formatting. The colour is not as important, but the comments are vital. We use Microsoft office 2007 here, on windows 7 enterprise.
View 4 Replies
View Related
Mar 22, 2007
I have a macro which opens one excel file, then copies the data into another, dead easy. However the first file is 'downloaded' from a bespoke package, where (for whatever reason) the package appends a number of spaces (" ") after data in one of the columns,
So sometimes the data will contain one, ten or more extra spaces (no telling how many) ie, it could look like "AB ", "AB ", or "AB " etc
Ideally What i need is a small bit of code that once the data has been imported to my sheet it can run and 'strip' extra spaces from the column, lets say column f, to leave all the data in this column to look like:
"AB"
"AF"
"CD1"
"VFE"
I am drawing a blank, any simple lines of code?
View 9 Replies
View Related
Jun 17, 2009
I'm trying to create a pivot table, but there is a problem. Although my data only has 2 years (1996 and 1997), it creates a new year it calls "blank," and a lot of the data that is supposed to be under 1996 and 1997 appears under this "blank" year. How do I fix this? See the attachments for the data and pivot table.
View 2 Replies
View Related
Nov 27, 2009
I am inputting 5 variables in columns A through E:
Family (Y/N)
Adult (1/0)
Child (1/0)
Date of Birth
Gender
I am attempting to generate:
Age Males in families sorted into age brackets
Age Females in families sorted into age brackets
Age Males single sorted into age brackets
Age Females single sorted into age brackets
I did this rather simplistically generating 4 columns (F through I) and then using COUNTIF (although I know there is a better way than the lame way I used COUNTIF but that is not the point right now).
However, my question is, can I generate the data described above (located in D13:I22 on my spreadsheet) without going through the messy, cumbersome step of creating extra columns?
View 6 Replies
View Related
Jul 24, 2014
Looking for a formula to accomplish the following:
I'm trying to populate cell A31 on a worksheet titled "VolumeTotals" with the data in Cell E23 from a worksheet titled "CurrentCustomers" if the merged cells F3-F22 on worksheet "CurrentCustomers" are equal to the word "Contract".
View 6 Replies
View Related
Oct 10, 2011
I am trying to do this preferably not in a macro if possible. Basically I have a monthly metrics spreadsheet that pulls it's values from a data entry sheet. I setup the display sheet so that if there's no data for the month the cell is blank in order to avoid errors. So, now what I want is one cell that will update and tell me if the last month's metric was good or bad. So, I am trying to create a cell formula that will be able to find the last row with data. In this case it would be the row corresponding to September's data, then evaluate the value in that cell to see if it's above or below 90% = 0.90 in this case.
View 6 Replies
View Related
Jan 4, 2014
I am looking to Find and Replace (via ctr-F) but I need to replace it only within a range of selected cells. Each time I try to do this it replaces everything even though I have tried selecting each cell and replacing only that. Is there a way to select a certain amount of cells and replace?
View 2 Replies
View Related
Jul 1, 2013
One of my datasheets by necessity ends up as about 25,000 rows long, but only about 40 of those rows have data in. The rest of the rows are empty.
If a row has data in, there will be data in every column on that row, for example if row 2 was not empty, there would be data in A, B, C . . . and all the way to the last column without missing cells along the row.
I need to copy those 40 rows without blank rows in between. At the moment, I have a macro that deletes blank rows, but it takes hours to run.
This is the code that's being used at the moment:
Sub DeleteBlankARows()
Dim r As Long
For r = Cells(Rows.Count, 1).End(xlUp).Row To 1 Step -1
If Cells(r, 1) = "" Then Rows(r).Delete
Next r
End Sub
View 4 Replies
View Related
Dec 26, 2013
I have a spreadsheet where a date is entered in column A with data in B-F. I am trying to write code to look in column A for a specific date and enter additional data into columns G-L on the same row. It needs to be able to skip the rows that are blank. So I have the dates of 12/1, 12/2 and 12/3 in column A rows 1,2 and 3 and I want to enter data for 12/2 on row 2 skipping the blank cells in row 1 for 12/1. Here is the code I have below.
VB:
Private Sub CBSecure_Click()
Dim my_name As String
Dim r As Variant
Dim l As Long
sFind = DockDoorCal.Value
If Trim(sFind) = "" Then Exit Sub
[Code]....
View 4 Replies
View Related
Jan 27, 2014
Script Example.png (Picture)
Script Example.xlsx (Example Workbook)
I have a report that I pull that I pull fairly often that is in this format and shows which footage of products each store is getting (out of over 1800 stores)!
Above is the format that it comes back as. And here is a spreadsheet that shows what steps I take to find correct values in detail.
So each FTG has an ID# and Desc. The ID is in text format and each ID is seperated with a comma, no space. Description also. Date is seperate by a space and comma. Stores change footages a lot so I want to find out which footage is effective today (1/27/2014) For ex: Store 63 would have the 5ft effective right now because we are between 5/23/08 and 5/22/14. So each ID# and Ftg Description is in the same order as Date.
When I have hundreds of stores, it is difficult to go through and get each one (even with the way I've been doing it.) But my ultimate goal is to create a macro to put only the current footage ID#, Desc, and Date in Columns C,D,and E. I'm not sure even where to start with doing a macro.
I usually just find the store with most ftgs, count them, insert that many rows after ID and Description columns, then do a text to columns (comma, delimited), sort by 2nd date column (so they all come up top) and then manually go through them and delete unneeded columns once I have them all.
View 7 Replies
View Related
Oct 26, 2009
I’m keeping tabs of some clay pigeon shooting scores. I go to alternate locations each week and normally shoot 100 clays, however sometimes it’s only 50. I’ve used MAX to find the highest score in Column D of a spreadsheet and it did what I required. However I now wish to find the highest number in Column D - but only include rows if Column C = P and column E =100.
Col B Col C Col D Col E Col F
date Location score out of % hit
25/10/2008A6210062
08/11/2008P3510035
22/11/2008A4210042
06/12/2008P6610066
20/12/2008A6010060
03/01/2009P295058
17/01/2009A5110051
31/01/2009P4010040
In other words I want to find the highest score for location P but only if that week it was out of 100 shots. It would return 66. Then I can do the same formula for location A and it would return 62. I can’t see how to do this and have searched the forum to no avail. It doesn’t look like I can just use the MAX anymore and I’ve tried incorporating that into a (nested) IF but unsuccessfully.
View 2 Replies
View Related
Mar 2, 2009
Table A: I have data separated by coma & space in cells A1:A4
A: Data Table
*A110, 20, 30, 40, 50, 60
211, 21, 31, 41, 51, 70
316, 19, 37, 48, 59, 81
418, 23, 33, 42, 57, 78
Table B: I want to calculate difference between cells A1:A2, A2:A3 and so on…
And if it is possible as shown in cell B2:B4
B: Result Table
*AB110, 20, 30, 40, 50, 60
211, 21, 31, 41, 51, 70
+1, +1, +1, +1, +1, +1
316, 19, 37, 48, 59, 81
+5, -2, +6, +7, +9, +11
418, 23, 33, 42, 57, 78
+2, +4, -4, -6, -2, -3
Separating cells A1:A4 data in separate cells can do this.
But if it is possible solution can be done as shown by VBA will be greater.
View 9 Replies
View Related
May 28, 2013
I have a sheet with 12 months in it, each in a column. As month's go on, I put the cumulative sales figures (YTD sales) in the given month. What I'm trying to figure out is how in the 13th column (i.e. the one after december), can I have it show the value of the most recent cumulative sales (i.e. if we are in May, there will be sales data for jan, feb, mar, and april and I want column 13 to show the value of April since it will be the month that last has data.
View 4 Replies
View Related
Apr 23, 2014
Basically I need a formula that work as below. It's over two sheets and is probably very easy to most of you I just cant get my head round this one!!
If B1 (sheet 1) = "TEXT1" ,FIND the content I1 (sheet 1) within a1:a3000 (sheet 2). If I1 is found in that range i need to know the text in the cell next to the found text.
E.g.: the text of I1 (sheet 1) was found in cell a234 (sheet 2) so it gives me the text in b234.
View 9 Replies
View Related
Aug 17, 2006
I have a worksheet that projects data into the future. Say 5 rows with each column containing a projection. In the last column (say 10) I want a formula that detects the cell to the left that first contains data and returns that data.
Each row will have data in different columns.
View 9 Replies
View Related
Feb 25, 2009
I have a spreadsheet that I have been entering and deleting data from. Currently there is data in A1:J1 only, however, when I Ctrl+End my cell focus is on J242. I have deleted rows 2 through 300 and cleared all from the Edit menu on cells A2:J242, both to no avail. How do I clear this so that if I Ctrl+End it goes only to J1?
View 2 Replies
View Related
Dec 12, 2006
i can use the VLOOKUP function to find entire cell in one table and paste the required data from the row....
Is there a function to search part of data and paste the required data
View 9 Replies
View Related
Feb 10, 2009
I am looking for a macro to do the below
copy range b2:b8 from sheet 2
find the first empty cell on sheet 1 row 2
paste values into there
View 9 Replies
View Related
Dec 30, 2013
I have a column with unique identifiers (numeric) that are included in another column. I need to find this number in the column and pull the data from that column into a new one. For example:
Unique Identifier: 28530
Is included in this string: 10.1.8........28530_201_10-....
I need the content from the string pulled into a new column. It seems there would be a LOOKUP that would work, but I'm not able to figure it out.
View 3 Replies
View Related
Nov 12, 2009
I'm trying to find a way to search a second sheet in a workbook for specific criteria outlined in a first sheet (in my attached example, from A3 downwards within the 'list of search criteria' sheet), and then to copy any secondary data found against a successful search match to the original sheet, transposed against its corresponding matched search term.
As you can see in the example, the search term 'bindi' (A4 in the 'list of search criteria' sheet) appears in the 'data' sheet 3 times - the secondary data for these occurences ('feathery', 'Fibonacci', 'glassy') is copied to the 'bindi' row on the first sheet and is offset with each copy to produce a transposed-esque effect of copy and paste.
If it's any help, there are a maximum of 9 matches for a single search term in the real document.
Thanks in advance for your help... I tried to adapt a previous solution given to me for a similar question but failed miserably. I bow humbly to your expertise!
View 9 Replies
View Related
Jan 13, 2010
In Sheet1 I've got a list of names in column A, then I've got a worksheet for each name in the list. I'd like to fill in column B with information from the worksheet of the same name as in column A.
Quick example...name in column A is Davis, want to fill in column B with a value from cell C3 on worksheet named Davis and do it for the entire list. Data being looked for is in same cell on each worksheet. Tried using a formula and dragging it down the list, problem with that is it doesn't change the sheetname just the cell.
View 3 Replies
View Related
May 15, 2009
Until now if I needed to find a cell's data (and its adjacent data) I've been using code in the format of (for the purpose of this example, you can assume all data in coulmn 1 is unique. Functioning as aprimary key using incremeting integers):
View 3 Replies
View Related
Jul 20, 2014
I need to write a conditional formula that will look up in a 2nd worksheet the last coloumn with data (note that there will be some coloumns in the row that will have data and some that will be blank, I need the last column for that specific row to be the return result)
View 9 Replies
View Related
Jun 29, 2006
Sheet2 has columns with dates as headings. I add data under each date as appropriate. I would like a summary page for Sheet1 that contains only the most recent column data from Sheet2 (and other sheets, of course). For instance, if I have dates for columns A-E on Sheet2, but only A and B have data under them, I would like Sheet1 to contain the data in Sheet2!B, but not Sheet2!A because that is old data. Sheet1 will only contain the most recent data while sheets 2 through "n" will have historical data. Attached is an example of what I mean...
View 5 Replies
View Related
Oct 13, 2006
I have the code below. and it works to delete the variable in DelWC which is selected by a combobox. Combobox is populated by Column R info, You select the data, and then hit the delete key, it deletes what it found, great.. but I need it to delete addition info that is in the next row adjacent to it...
Column R Column S
AINA2 X-5900
AENE2 X-5874
The code finds and delets the AENE2 but doesn't delete the X-5874
With Worksheets("Data"). Range("R:S")
On Error Resume Next
Set Del = .Find(DelWC)
If Del = Del Then
Del.Delete Shift:=xlShiftUp
View 2 Replies
View Related
Nov 5, 2006
I need to write entries into an open spreadsheet with data input on a userform.
i need to use the xlup facility to find the last used row in the spreadsheet, select the next line and then enter the data, but how to return to VBA the actual cell reference that has been selected after doing the xlup and down one row. I need to be able a total potential 31 rows of data from the userform.
View 5 Replies
View Related
Dec 12, 2012
I want to extract the following details into another collumn (bold ) from data in column B1 into C1. The information i want begins with 3 digits with a '/' then other digits follow.
M / AS308 - :308/65588 POUN
S33 / 33 - :133/01504 TR
NI101 - :101/915635 T
N101 - :101/915635
View 4 Replies
View Related
