- In column A, I have list of items that take up 700 cells (i.e. the text is in cells A1, A2... A700).
- I have applied conditional formatting to these 700 cells, so some of them are now highlight YELLOW based on a criteria (i.e. cells A3, A14, A422, A654 are yellow)
- I want to create a list of these yellow cells in column B.
how to add alternating cells when all are numbers, e.g. B8 to KW8 are all numbers and I want to add B8, D8, F8, etc. until KV8 and then separately add C8, E8, G8, etc until KW8. Is there a more efficient way to do this than =sum(b8,D8, etc)? I also need to do the same with =counta(b8, d8, etc).
View 2 Replies View RelatedIn a long string of characters and values, for example in B2:
Cos PGA 3.00 8.98 0.75 Cis TAK 1.00 2.99 0.25 Gsr PAM 14.00 41.92 0.82 Art GUN 3.00 8.98 0.18 Tlu NRG 9.00 26.95 0.33
I want to extract the second value after the specific string which is in in column headers C1, D1,E1...:
Art GUNCis TAKAxa MLPGsr PAMPen NMECos PGATlu NRGRot KGR
so for the example the result should be 8.98 in cell C2; 2.99 in cell D2; no value in E2; 41.92 in F2...
I attached a sheet with the table.
I've got a string of text (that can vary in length) and contains a date (could be any month)..
I'm trying to extract the date from the string using VBA.
For example, the string may be:
"The following price of the service is effective until July 1, 2012 and may change whenever"
I need to first determine if the string has a date in it and then extract the date....
I did a search on mrexcel.com and found the following formula that will tell me if the text has a date (returns true/false)...however, i'm not sure how to use this in VBA nor how to find location of date....
=SUMPRODUCT(COUNTIF(B1,"*"&N73:N84&"*"))>0
B1 contains the text, N1:N12 contain the name of the months....
I have in my cell a number, namely, 5260007005020024100055040300004110000000
What I would like to do is extract a set of digits from it,
Starting from the second 2, and shown here in the dots. I need all of the numbers in a separate cell, "52600070050200 .24100055.. 040300004110000000"
Hope this is clear. bearing in mind the number will remain as one so I would need to start at 14 then using LEN or something I'm not sure, extract the following 8 into another cell.
I have two lists. A1:A7000 has 7000 items and B1:B10000 has 10,000 items all in random order. List B includes all the items on list A, but with an additional 3000 items. How can I extract the 3000 items that are exclusively on list B? I would like to put these on a new list C1:C3000.
View 6 Replies View RelatedI have set up a 'week of' calendar for a year. This allows users to enter data in a cell for the week. For example, the today would be displayed on the calendar, the 11th.
What I would like is, in a separate tab, to list (in list format) only dates with data entered.
How can I write a formula that checks a cell, and displays the data, but if there is no data, it goes to the next cell (to the right). In the new tab, I would like the data to be listed top to bottom even though the calendar is left to right.
It would also be cool if in a separate column in the new tab to display the date, but im not sure how because the calendar isn't of actual dates, but of a few cells on the top with the month, and the actual number is in the cell.
Right now I have this as a start, but it's not much...
=IF(Category!C11>"",Category!C11,"")
I have attached an image of how the calendar is set up.
I have a list of email aliases for my company, approx 10,000 entries, mapping the alias to an email address. Two columns, one the alias firstname.surname, second column being the full email address. I have a second list, a subset of these aliases, approx 1200 of them, that are users of a particular web app. All i have in this list though, is the firstname.surname bit.
What I need to do is compare the lists, and where the aliases match I need to pull out the corresponding full email address from the long list, so I have 1200 aliases in the shorter list, with the full email address mapped to it. Is this possible in excel? i've tried all sorts of LOOKUP functions, and MATCH functions, but none seem to work. MATCH will give me the corresponding row number in the long list, but then it seems to offset itself every row it goes down, so the values are 1 out for every row. First row is good, second row -1, third row -3, fourth row -4 etc.
I don't want to have to manually go through 1200 entries and match them against a 10,000-entry list. Hopefully there's a quick way in excel to filter the long list down to the entries specified in the short list?
I am trying to sort a fairly long list of tasks into a custom calendar order. I have created the custom list as Sept, Oct, Nov, Dec, Jan etc through to Aug, with one additional heading of "Recurring". The list won't seem to sort by the column where these headings occur. See attachment.
View 7 Replies View RelatedAn original text string entry appearing in an Excel cell would be:
"N7C Neuroprotective J5Z Antiviral, other M2Z Antiarthritic, other J5A Antiviral, anti-HIV"
I need to extract N7C, J5Z, M2Z and J5A from this string and list these alphanumeric values in separate cells adjacent to the original text string. The challenge is that these alphanumeric references may appear in different positions within the original string with no fixed value e.g. a "," separating them. The alphanumeric references may also be 3 or 4 characters in length and there may be different numbers of alphanumeric references in the original string.
Another example would be (very different from the first):
"T2Z Recombinant, other K5B Radio-chemoprotective J3Z Antibacterial, other D3A Vulnerary A10C Symptomatic antidiabetic K6Z Anticancer, other R8A Antiasthma B6A Septic shock treatment I1Z Immunostimulant, other S1Z Ophthalmological R8B Antiallergic, non-asthma M1A1 Anti-inflammatory"
You can see that in this further example "A10C" & "M1A1" are 4 character alphanumeric strings wheras the others feature 3 characters.
My data is set up as
A
B
C
1
A
B
C
[Code]....
How Can I make that data appear as
A
1
A
2
B
[Code]...
Where is takes the row and keeps transposing it down 3 lines at a time?
I have a spreadsheet with only entries in column A - 154 cells. Nothing in other columns. When I try to print, it says it'll be 4 sheets long...
1
2
3
4
down to
154
How can I get it to print in a snake-like fashion so all on one page?
I need to create a new sheet, and pull all the rows (14 columns each) from another sheet in the document that meet a certain criteria. Fortunately, that criteria is all in one column; unfortunately, out of about 30 different entries in that column, I need it to pull 7 of them, 4 of them have been input 2 or 3 different ways.
I know I can go through the list of 900+ by hand and copy the rows, but there has to be an easier way right? Here is the list of all the spellings of the ones I need to pull..
D.C.
DC
DPM
DPT
Ed.D
EdD
EDD
JD
Ph.D
Pharm. D
PharmD
PhD
I have a long list of names in Column A sorted alphabetcially. I dont know how long the list is.
I wish to find the start and end row numbers of names that begin with a specific letter, for example if the letter is B I want the first row number that starts with B and the row number that ends with B. Assuming the letter I want to use is in C1, here is what I came up with:
Excel 2003. When the Vlookup List becomes impractably long to select data from the cell dropdown. Is there an alternative prefered method to entering data from the long list?
View 7 Replies View RelatedI've been given a long list of mailing addresses in Excel in formatted as a single column, each address using three rows with no blank rows. Example:
John Doe
123 Maple St. #2
Cambridge, MA 02139
Jane Smith
321 Elm St. Apt 24
Austin, TX 34557
etc.
etc.
I need to get this data into the form ...
John Doe 123 Maple St. #2 Cambridge MA 02139
Jane Smith 321 Elem St. Apt. 24 Austin TX 34557
I would think this has been done, but I've searched the web and this forum without success.
I've done some looking around in the object browser and in the forum, but i wasn't able to find the answer to this question:
I've got a combobox (dropdown list) in a userform which i use to run macros. soft of them are fairly long (2-3seconds), and during that time the dropdown list remains visible.... is there a way to force hide it?
I'm doing the website for my company and things have to be entered into the site via spreadsheets. I have a list of numbers
e.g. 3652, 2845, 50925, 4809, 18392
that need to have the spaces removed in order for them to work.
e.g. 3652,2845,50925,4809,18392
There are over 500 cells with multiple numbers in each, so I don't want to do it one at a time, and rightly so, because Excel keeps changing the format and giving me something like
3.6*10^30 and loses all my commas.
Is there an easier way?
I have a list full of different IDs with different version numbers (This format: AKH123.1).
I'm trying to clean the list - removing all duplicate IDs but leaving only the largest version number.
I managed to remove the last digit using LEN/VLOOKUP and removing duplicates, but leaving only the largest version number is too tricky for me. I've thought about trying something with LARGE but can't wrap my mind around it.
My thought process went like this: I want to find all occurrences of "AKH123" and return the largest one.
The list contains about 8000 entries and this would save en enormous amount of work.
Here is a tiny portion of the list:
AKH450.1
AKH451.1
AKH451.3
AKH451.4
AKH451.5
AKH451.6
AKH451.7
AKH451.8
AKH452.1
AKH453.3
AKH454.1
AKH455.1
AKH455.2
I have a sheet with about 20000 rows consisting of hundreds of names which
occur randomly.After each name is a date of the type 29-Jan-06.The dates are in ascending order.I wish to print in a third column how long it is in days since that name appeared previously in the list,if it never appeared before this will be zero.
I have a spreadsheet of barcodes 114,618 rows long. I have a program that requires me to enter in each and every barcode in a certain format. This is the format that it requires me to place the barcodes into.
23423432, 23432342, 234324323, 234322344, 432432432, .............
so and and so forth.
Only problem is my POS system exports the barcodes in a format that looks like this:
545554545435
345435435435
354543534534
354534545453
Now I have a Macro to add a comma to the end of these barcodes. That part was a breeze, however, I have hit a brick wall when it comes to placing them into an ongoing sentence type format, that my program needs. I have found several ways to combine and merge and "justify" the content, however these typically only cover small amounts of content, and doesn't even come close to hitting anything over 100,000. I don't know if maybe I need to be using another program in tandem with Excel.
This code is taking way too long to display the actions that it executes. It didn’t used to be that way. I was wondering if anyone knows why this may be. The Excel file is large – over 8 MB.
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "N:N"
Dim Cmnt
On Error Goto ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
If .Row > 3 Then
If Me.Cells(.Row, "N").Value = "" Or Me.Cells(.Row, "N").Value = "O" Or Me.Cells(.Row, "N").Value = "H" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 0
End If
If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "DR" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 39
End If
If Me.Cells(.Row, "N").Value = "C" And Me.Cells(.Row, "O").Value = "HJB" Then
Me.Cells(.Row, "A").Resize(, 26).Interior.ColorIndex = 6
End If.....................................
How do i separate example: #EM/13/10000749#JTDEl_1234#abc-NORTH#1##No Traffic Data into separate cells like 0749 to A1 , JTDEl_1234 to A2 , abc-NORTH to A4 , 1 to A7 , No Traffic Data to A5
And then continued with another text of the same standard to the next row by just copying from "Notepad" and pasting it in excel.
All the text is following the same standard.
Example :
#EM/13/10000749#JTDEl_1234#abc-NORTH#1##No Traffic Data
#EM/14/10000730#JTDEl_1256#abc-SOUTH#3##No Connection
#EM/09/10001683#JTDEF_5674#abc-EAST#2##Low Data
I have a need to combine multiple columns into a long list of results in 2 final columns. Here is my example...
Current layout with varying number of data points per row...
ColumnA | ColumnB | ColumnC | ColumnD | etc...
example1 | Test1 | Test2 | Test3 | Test4
example2 | Option1 | Option2
example3 | Number1 | Number2 | Number3 | Number4 | Number5
Desired Result
ColumnA | ColumnB
example1 | Test1
example1 | Test2
example1 | Test3
example1 | Test4
example2 | Option1
example2 | Option2
example3 | Number1
example3 | Number2
and so on and on and on until all rows have been filled in down the file...
Is this possible?
I need a formula that will search the range D8-D100 to confirm that all cells within that range are either 11 characters in length or blank. I will use it inside of an error message that will look something like this:
=IF(****formula that checks to make sure all of the cells in that range are blank or 11 characters****=TRUE, "", "Please make sure that all cells are 11 digits or blank")
I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.
View 5 Replies View RelatedI have two conditions setup in Options>View - Zero Values.", "style="background: #FFFFFF;padding: 2px;font-size: 10px;width: 550px;"");' onmouseout='GAL_hidepopup();'>formatting.htm" target="_blank">conditional formatting. The first is setup for alternate row coloring with this formula inside Conditional Formatting:
Formula is = MOD(ROW(),2)
My Second Condition is
Cell Value is between $P$10 and $Q$10. This sets the font bold and a different color. The two cell values are two dates. I want to change the cells font color and bolding as long as the value is within that date range. It works fine, but for cells that are on the row that is colored the second condition doesn't apply for some reason.
I have a list of U.S. cities and their crime numbers. The list contains roughly 8700 records. What I need to do is pull the records for 10 specific cities from that list. Exactly which cities are singled out may change from year to year.
And this is something I'll need to do every year when the crime stats are released by the FBI.
I have some code that is importing data from a text file that contains ~35,000 line items and is ~50 columns wide (this part of the code is working fine).
Once the file has been imported, I need to copy certain line items to a sheet called “output”. The code would be something like this if reading line by line:
If the value in row x, column 5 (it is always in the 5th column) in “AccountList” then
copy entire row and to next empty row in sheet(“output”)
The “AccountList” is separate sheet with a list of account numbers in column A with ~250 items.
I am not sure if the code to extract this data should occur while I am importing the data or if I should do it after and run through the data again.
I have data for the distances of cars journeys. I'm looking to extract the 1st journey distance for each date for each car. The car registration number is in column A.
This is an example:
Sheet1  ABCDEF1Reg number Date distance  1st Journey distance
210D1449904/01/20119.271Â Â 9.271310D1449904/01/201110.016Â Â 1.876410D1449905/01/20119.292Â Â Â
510D1449905/01/20119.629Â Â Â 610D1449906/01/20119.327Â Â Â 710D1449906/01/20113.185Â Â Â
810D1449906/01/201110.163Â Â Â 910D1555506/01/20111.876Â Â Â 1010D1555612/01/20119.534Â Â Â
1110D1555712/01/20111.48Â Â Â 1210D1555812/01/20115.628Â Â Â 1310D1555912/01/20115.356