i have a column (A) with a bunch of "K"s in it....scattered through 150k rows.
in column B, i have values for every row.
What formula in C1 will search col. A and return B values for All "T"s in column A?
I want to bring some scattered rows in data range directly underneath the appropriate columns
Here is the description of manual way of doing the same task.
1. first, I check for last empty rows in column "M" i.e for country(By pressing CTRL + Down arrow key in column "M") and then i look into the immediate next empty rows where is the date values, usually i found it into next rows but in a different columns. so i copy the range one cell before from date values till last columns in that same row and i bring it back to the original row where it should be indeed.
For example after pressing CTRL+down arrow key in columns "M", If i see date values In next immediate rows "E7". I copied from "C7" or "B7" (one cell before date value cell) ,depending on In which cell the date value is sitting till last columns i.e "N7" and paste it manually to "D6". and I do repeat the same work manually till i aligned all rows to their proper place.
I am attaching my file also.
I have a column of cells over 2000 rows that have a bunch of text in each cell. In the middle of this text in each cell are websites (not hyperlinked). I was wondering if someone wrote a macro or formula to extract URLs located in a bunch of text? I can parse this out but doing some delimiting but that will take longer.
View 9 Replies View RelatedI have three colums of data.
First represet company , second colum represent data1 ( x- axis) and third represet data2 ( y-axis).
I want to plot x-y graph with the company name
Eg.
McDon | 10 | 20
In the above case i want to show a round circle or dot which will be 10 right on x axis and 20 up on y axis and near the dot i want to show McDon as company name.
See that attachement... Graph is coming well.. but i am not able to add the company name to the dots.. or pot of x and y intersection
I need to do is be able insert a bunch of pallet references (manually, you will see a tab called 'Pallet Check' on the workbook attached) and then click a button that will check to make sure that those pallet numbers have been entered onto that workbook on one of the sheets from sheet1 to sheet100
If they are all ok I want it to return a value 'Pallet Reference Verified' in the column to the right of that pallet reference (column B) if it doesnt find that pallet then to return a value of 'Not Found'
I would also like to know if it finds any duplicates and if it does then return the names of the sheets that the ref is duplicated on (something like..... sheet3; sheet87) in column C for that ref i.e. pallet ref 'R6 10' has been entered so it will come back as being verified BUT it is also on another sheet which is bad so I want to know that it has been entered twice.
I just inherited an excel spreadsheet that has 10 tabs per every month in the year. Right now it is not that bad as there are only two months in the spreadsheet but I can see this being a headache to tab through later on in the year.
My initial thought is to make a table of contents worksheet that has a bunch of buttons (with macros) to hide/show tabs based on month. (Note: It would not make sense to consolidate the month worksheets into one as each worksheet already has a lot of data in it to begin with.
I have forms filled out by many, and I need to compile the information in it to create a list in my summary.
Is that possible?
fortunatly, the information always resides in the same cell on every file.
I need to combine the time scattered in different columns and align them daywise.
View 3 Replies View RelatedI have data that is something like this:
Group Assets
1 200
1 300
2 150
3 50
1 500
3 100
I want to create a scattered plot charts based on the data above but I cant get it to work for some reason. I want to have the "Group" on the x-axis and the "Assets" on the y-axis. I don't want to combine the same group number (for example to have group 1 with total assets of 1000, I want to keep it as 200 300 and 500 because I want to show which group is more represented than other group.). I have tried sorting the data by group and also with out sorting but the data doesn't plot at all (everything looks clustered at the 0 level on the x-axis instead of the respective asset level.
I have four sheets workbook, and i'd like to set a command button that prints scattered tables ( ranges) on my workbook but still have a pattern.
I attached a simple wokbook that explains how ranges are positioned in my workbook, i hope that it is possible to print these tables in the sequence shown in the file by a single command.
I have 2 columns of data E and F. Column E has 11 different words that randomly repeat, Column F has 10 years of dates, about 1,000 entries (10/11/12 format). Both columns values come from formulas.
I am trying to copy cells E & F to columns K & L starting in row 2 only if there is is data in column E (one of the 11 words) and skipping all others rows. Both the E & F values of tthe row must be copied together, i.e if text is in E45, then copy E45 and F45 into column K and L starting with K2 & L2. This is a task which will be repeated multiple times as data is replaced in columns A-D.
I want to compare two columns. I would like to see if the contents of column B appear anywhere in column A, for any amount of rows, and if it does, to place the match in col C.
So in the example below, red in B1 would be checked in A1 through A1000 or however long A is. When any row has red, place that match in that row for C.
This would be repeated until all rows in B are complete.
Example
Col A: red
Col B: red
Col C: red
Col A: red
Col B: blue
Col C: not found
I am working in the attached spreadsheet. For anyone who does not wish to open the link, let's say these sheets are titled 'Sheet1' and 'Sheet2'.
Sheet2 looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
[Code]....
Now, how may I pull this data into a summary sheet (Sheet1) that skips over any instances where column A ("Year") is blank so it looks like this:
Year Cat Name
2013 4 Sal
2013 4 Pat
2013 3 Pat
2013 2 Pat
2013 5 Bob
[Code]...
Note: All columns are the EXACT same across each sheet(including Sheet1, Sheet2, Sheet3), if that extra piece of information works.
PS It is fine if, in order for a formula to work, I need to copy and paste the formula for the length of rows per each Sheet to consolidate.
EDIT:
So, in my industry, any summary-level data (shown on Sheet1) must have a linked reference to a particular sheet (Sheet2, Sheet3, etc.) so the user knows where the raw data came from. This is to ensure increased accuracy of data and minimize human error (like, if for example, I accidentally didn't copy and paste all the rows correctly).
I have uploaded the actual sheet to show what the data actually look like. I am trying to pull in 2013 data into my "Summary Page" and would like to have all the data linked to the '2013' sheet but skip any blank rows.
Here is my failed formula:
=INDEX('2013'!F$3:F$338,SMALL(IF('2013'!$E$3:$E$338<>"",ROW('2013'!$E$2:$E$338)-ROW('2013'!$E$2:$E$338)+1,ROWS(B$1:B1)),""))
I am working with an excel spreadsheet. There is a column for "name", and then the next column is for medical condition.
If the same patient has multiple (say, 4) conditions, the rows for "name" are combined into a single cell(let's say, a cell going from row 2 to row 5, witha a single name in the combined cell) , with the next column having four different cells adjacent to the single cell (cell 2 has "bleeding", cell 3 has "fever", cell 4 has "diarrhea", and cell 5 has "rash"). Some patients have only single conditions, single cells.
Will this mess up COUNTS and COUNTIF?
Is there a way to split the combined cell2-5 for ''name" into four cells all containing the same name?
I need to combine rows that have the same value in column a and column b to the same row by offsetting column c to the next available column. For example, I would like the first 6 rows of the provided sample to appear like this.
0014B22<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities<@44>Soil Properties and Qualities
0014B23<@28>Coursey<@28>Ogles<@28>Shelocta
Sometimes the values are the same in column c, sometimes they are different. I do not want to delete duplicate rows where they are the same. Sometimes there are 2 rows that have the same values in column a and column b, other times there may be 3 or even 4 rows with the same values in column a and column b. Regardless, I would like the values in column c combined on the same row in the next available column. It would be nice if the duplicate column a and column b rows (with a null column c cell) were then removed, but I could do that in the next step.
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B22<@44>Soil Properties and Qualities
0014B23<@28>Coursey
0014B23<@28>Ogles
0014B23<@28>Shelocta
0014B24<@33><i>Available water capacity:<p> High (about 11.5 inches)
0014B24<@33><i>Available water capacity:<p> Very low (about 2.9 inches)
0014B24<@33><i>Available water capacity:<p> High (about 9.0 inches)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> High (about 1.98 in/hr)
0014B25<@33><i>Slowest saturated hydraulic conductivity:<p> Moderately high (about 0.57 in/hr)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B26<@33><i>Depth class:<p> Very deep (more than 60 inches)
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches
0014B27<@33><i>Depth to root-restrictive feature:<p> More than 60 inches..............
I've a worksheet that is 3750 rows of cells from A to DT and contains only numbers with the exception of a header row. The rows are in groups, primarily 4 rows, although there are exceptions - these rows are separated by blank rows. Small example as follows.
0:001:002:003:004:005:006:007:008:009:000000-296567-6-1-400000286567-300-230000715557-16-11-40-18141755226-348405-362203081417192125-893581061800-2443-11124-289326-81318-1105027854331331211318-10216-161235526785691450451115951-17-1882505656683093102671056-191-33-2710000554740971-61-78-34224107-169-128-17090179-85220-55-63-1-162-140-242-235328266319106113-214-64-148205-4108120-142076-167-60-68-6418081102-800000000000-3099695807185-80356138-123194186210677727125-23233092-2942944748055564476-422051-224-34461486649597117-84209-22-251-94
I have been trying to conditionally format each group of rows and each column and color the cell in each column of four (or less) numbers in each group that represents the maximum number.
It was easy enough to come up with the conditional formatting BUT it will take forever to do this manually. I've been told to use a macro but I know nothing of programming. Has anyone seen or heard of a macro that might already be written to perform this onerous task.
I have two columns with dates. Colum A has the date of the deadline of a document while column b has the day it was sent. Column X will display the difference if its a positive integer ( i.e. if the document has been sent after the deadline). Now i have another column Y which displays the month as an integer of when the document was actually received.
Now i need a counter which will count the number of instances a positive integer is registered in column X according to the month in column Y. I have been trying everything but cant figure a simple way to do it. Im doing this so i can be able to see how many documents are sent after the deadline per month.
I have a sheet where i want to delete duplicate rows where column A and column B combined are equal, i.e. range(Ax:Bx) where x is the current row. I am using the macro below but cant seem to get it working as I keep getting a type mismatch error and Im not sure why.
View 2 Replies View RelatedI have an excel spread sheet with several rows of 265 (9A-IV) columns each with a heading. I would like to transpose the worksheet columns so that the heading is placed in Column A against the corresponding that is placed in column B. For example
ABCD14692571038Transpose to A1A2A3B4B5C6C7C8D9D10
I want a cell on one spreadsheet (SP-A) to add up a column (M) in another spreadsheet (SP-B) rows 11 through 10000.
Here's the thing, I want that cell to actually display not the sum that it gets but that sum minus all numbers (in M column again) which column E is filled out with any data for their given row...
i'm not even sure if this makes sense lol... let me use example
on SP-B there's a column M.
in row 11, value = 3
in row 12, value = 5, in this same row column E is filled with whatever
in row 13, value = 2, in this same row column E is filled with whatever
in row 14, value = 6
in my SP-A I need the cell to display 9, since rows 12 and 13 have values in E and I don't want to add those to the sum.
I have a table in the format below. Pin_11 in this table is CLK and Pin_12 is STRB. I want to replace Pin_11 with PIN_CLK. Basically I want to look for CLK in Column3 and record what ever is in Column1 and Column2 on the same row (base row). For every repetion of that value in column1 and column2, in the rows above and below the base row, I want to replace column 2 with PIN_<column3 value>.
See below for initial table in column 1,2,3 and final result in column 6,7,8. I have a huge file this format of data that I need to end and was wondering if there is a script or formula to do it.
CARD NO
PIN NO
TYPE
CARD NO
PIN NO
TYPE
CARD_2
PIN_11
[Code] ....
I believe that the best way to evaluate this request is to look at the example. I have 2 buttons to demonstrate what I need, along with written instructions.
eMailSampleTest.xls
Following up from Insert Blank Rows Where Date In Column D Changes from the first populated row after the inserted rows, and paste it in the 1st blank row above the populated rows (as a kind of title). How would I build this into the code below?
Private Sub CommandButton1_Click()
For x = Range("D65536").End(xlUp).Row To 7 Step -1
If Range("D" & x) <> Range("D" & x - 1) Then Range("D" & x, "D" & x + 3).EntireRow.insert
Next
End Sub
have a large dataset where I want to move some data from rows into columns and other data from columns into rows. I have enclosed an example. It is indeed only an example as the original has many more rows and colunms. What I want to do is to move the the years (columns) into rows and move Product (row) into columns. The example shows how it currently looks and how I want it to look.
View 2 Replies View RelatedI've got a good-sized single sheet workbook (A1:I8851) that I need a fairly complicated (seems like it to me, anyway) macro to get data in line from column to column. Here's what I've got:
Column A is titled "Category", and Column D is "Description". The actual category listings are in merged cells, spanning anywhere from 5-12 rows. Column D has no merged cells, but many (not all) cells in D contain data carried over from the cell above it (instead of wrapping the text, for some reason it just inserted into a new cell). The number of merged cells in A is equal to the number of unmerged cells in D.
I need a macro to combine each of the multiple rows in D into one single row, stopping and starting over when it reaches the next Category row. That way each category will have a single description line without incorporating parts of the description from the category above or below it.
Count all the true statements in column A (Work) of sheet1 (Checklist), once counted insert that many rows on sheet2 in a specific location, I found a count formula just don't know how to do the insert rows part
Code:
Sub CountRows()
Dim Rng As Range, CountTrue As Long
Set Rng = Sheets("Checklist").Range("Work")
CountTrue = Application.WorksheetFunction.CountIf(Rng, "True")
End Sub
There are groups of similar ID numbers in Column J. For a group of similar ID numbers in consecutive rows there is only one row that has a number greater than 0 in its Column L cell and the rest of the cells of Column L for that set of similar IDs is filled with 0s.
First for that unique ID group I need to find out which row is it that has a value greater than zero in its Column L cell.
Then I need to use that value to fill the rest of the 0s in Column L corresponding to that set of Unique IDs.
The process continues with identifying similar IDs in Column J and this time doing the same thing for their Column M. I have attached a sample file that shows the data and how the results need to look like.
I've tried for a week to figure out an excel formula. I have a spreadsheet that I add daily sales numbers to and they get recorded to a column in a different area within the spreadsheet. I would like to know if there is a formula that will sum the last 5, 10, or n rows in that column automatically. My range is K2:k265 and the last rows in that column are "0", since I have not filled in the sales number that far yet. Hope this explains it enough for someone to figure out. I don't explain things very well in Excel i've learned.
View 9 Replies View RelatedI know this has been asked similarly before, but I can't seem to adapt any old answers to my problem. I have a spreadsheet with lots of data that has values listed in rows of 5:
1 2 3 4 5
6 7 8 9 10
...
and I want to change it to one long column:
1
2
3
4
5
6
7
8
9
10
...
I have over 1000 values, so this macro or whatever would need to be able to do its function over and over. Attached is a raw data sheet, and my desired result.
http://www.yousendit.com/transfer.ph...D2E4ED0D9DFF93. It is a very simple change that I need, but it seems to be hard to repeat it over vast streches of data.
=SUMPRODUCT(C1:C3,1/(D1:D3))
Trouble is sometimes the value in D1:D3 could be 0 or nothing. how do I get the formula to only sumproduct the rows where the value in Column D is not a 0?
I tried the following
=SUMPRODUCT((D1:D3<>0)*C1:C3,1/(D1:D3))