I true Excel novice here-
We have a BUNCH of data we need to get in to a spreadsheet format-
can anyone kindly tell me how to do the following
985-0050
3PAR ADVANTAGE, PRICE PER NODE FOR 4, 6,
3PAR ADVANTAGE, PRICE PER NODE FOR 4, 6, AND 8 NODES
$14,876
we want the data to go from 4 seperate rows to four new columns
PART NUMBER, DESC1, DESC2, PRICE
I'm trying to move multiple ROWS to COLUMNS. My data looks like this:
ID CLASS_NBR1 UNITS_TAKENGRADECLASS_NBR2UNITS_TAKENGRADE CLASS_NBR3
000555 688 1 A
000555 567 3 B
000555 888 1 C
000555 999 1 B
000555 889 3 C
101999 889 3 A
101999 567 1 B
I have between 4-9 rows that I'm trying to move to columns. I'm attempting to move data for each student on one row, currently each student has between 4-9 rows.
I've tried so many different formulas and MACROS that didn't work I started doing this one by one (manually) but I have too many rows and it's taking too long.
Im having a little trouble with this, Im dealing with a list, and I need to change the colour of rows, based upon a value in 2 different columns.
Im close, but cant quite get it.
Basically its like this, the data in these cells will only ever be either; blank in one or the other, both are never blank. or a number up to about 3 or 4, usually 1 or 2.
I have data in cells A2 - N2 and cell O2 is currently blank. I am trying to set conditional formatting where if O2 has a value entered into it, it will conditional format A2 - N2 and highlight the entire row red.
I tried a formula like =O2"" but that does not work. Also tried a few other things but no luck.
Also, once the formula is set, what is the best way to apply the conditional formatting to rows 3 - 30. I tried the format painter tool but that seems to want to extend the formatting range from rows 3-30 and not just per row.
I have been trying to find a way of formatting an entire row based on the contents of cells in each column. However i come unstuck when trying to make the column dynamic. Below is an example:
Month
2010
2011
2012
[Code]....
The idea is that i say current year is 2011 and all rows where C contains an a will turn green. What i would like to achieve is that when i change current year to say 2012 the conditional formatting adjusts so that it looks at D instead of C.
I'm looking for a Conditional Formatting formula that will check two columns before highlighting the duplicate rows. I need it to be conditional formatting because I know nothing about writing macros or vba (what-ever that is?). Data is entered into Columns A, B, and C. I need to check both column A and C before it highlights the duplicates, based on those two columns. (The format only unique or duplicate values checks only one column.) I have attached an example, but this is just an example, as I have hundreds of lines to go through on the original. (For this example, Row 2 and Row 7 are the duplicates I need highlighted.)
View 3 Replies View RelatedI'm using Conditional Formatting for an entire column to check the value of each cell and compare it to the value of a cell in the same row but a different column. (Cell Value equals =$D2) It works great until I insert or delete a cell in the formatted column. The reference does not change as one would expect. I've played around with formulae such as =$D2<>$P2 but the reference only changes for the P2, not the D2. I've also tried using =CELL("contents", ADDRESS(Row(),4)) but this causes excel to complain.
View 3 Replies View RelatedI've got some time values in an Excel Sheet in the format hh:mm:ss. I need to split them into columns (including the colon) like below:
hh: | mm: | ss
I can do this manually using text to columns but when I use text to columns in my macro, it automatically changes the time format to h:mm:ss PM
i need to mark some row (which has some content written in), mark other row(with data too) and switch/transpose them mutual. when i was trying transpose method, which is using for switching rowns and columns, it wrote me error, that data are overlapping. it means it cant work on same things (rows > rows, columns > columns).
View 14 Replies View Relatedremoving duplicate rows and move other data frm rows to columns.xlsx.
I am attaching a sample excel sheet showing what I need to do.In the first tab, I have a list that includes duplicate rows (first column only). I want to remove those duplicate rows but I don't want to lose the data in the following columns which can be unique or duplicates as well.
see the desired result tab in the sheet to get an idea of what I am looking for as the end result.
Keep in mind that the actual source file I am working with could have up to 50000 row, and the expected results could be around 2000 rows. So nothing can be done manually.
I have this in Column A, with about 120 entries:
Company 1
Contact 1
Address 1
City, ST, ZIP 1
Phone 1
Fax 1
Company 2
Contact 2
Address 2
City, ST, ZIP 2
Phone 2
Fax 2
Company 3
Contact 3
Address 3
City, ST, ZIP 3
Phone 3
Fax 3
I want this:
Company 1 Address 1 City, ST, ZIP 1 Phone 1 Fax 1
Company 2 Address 2 City, ST, ZIP 2 Phone 2 Fax 2
Company 3 Address 3 City, ST, ZIP 3 Phone 3 Fax 3
all the way down.
I can't figure out how to record the macro to tell it to then skip the blank line, collect the next set of data, and put it in the next row. I can do it for two, but then it just replaces the first two with the next two and I lose data.
Column A is numbered 1 -100 successively Column B thru D contains data that goes with the assigned number in column A. I need to be able to move rows of data in column B through D to a different set of rows all at the same time (not one cell at a time) without disturbing the set numbers in column A. And with that, have all the other rows of data automatically adjust accordingly(not to be deleted or replaced).
View 1 Replies View RelatedI have individual data files (one for each participant). Each file has 64 rows (one for each trial in an experiment) and 18 columns (one for each variable). I would like to create a master data file in which each participant is represented in a single row. I will then have 1152 columns (18 columns for the variables, repeating 64 times so that each trial is represented).
In other words, my individual data files look something like this:
Participant 1 (title of data file)
Trial/var1/var2/var3
1 55 43 65
2 54 43 56
3 33 23 56
and I want to compile all the data into a single file that looks like this:
Participant/Trial1_var1/Trial1_var2/Trial1_var3/Trial2_var1/Trial2_var2/ ...
1 55 43 65 54 43
2 . . .
I know how to record macros and use IF, THEN, AND, & SUMIF statements creatively, but that's about it. I was hoping to record a macro that I could use on each individual data file to turn it into a single row, and then paste each row into the master file.
I have to format huge text file to columns. Here is the file: [URL] The whole file should look like the first 15,16 rows. What is the fastest way that can be done? Here is the original file: [URL]
View 3 Replies View RelatedHow to align two columns that sometimes have rows that are unequal in number. The data is sourced from a web page that has a table layout and the table lays out the one to many values this way in the table. My data looks like this:
Table ...Term
t1 ..... term1
t2 ......term2
..........term2.a
..........term2.b
..........term2.c
t3 ......term3
..........term3.b
The editor removes leading spaces. In the column Table there are only three rows but in column Term there are multiple rows per single row from column Table. So one row t2 for column Table has three rows in column Term. Are these cells merged and can I in a mcro detect if the cells are merged? How is the addressing of the merged cells in a VBA macro?
I have prapared a formula for B2,B3 and C2,C3 and the reult came in B10,B11,C10,C11 and D10,D11,E10,E11.
From that relust i want to get the final result should be as follows.
B10 should again divided in to anil(in one cell) and 16/12/2013 11:33:50 (in another cell). This formula to be added in B10,B11,C10,C11 and D10,D11,E10,E11 formula.
I loooking for the final reslt, i have explain the in the excel. I reuired the final result with single formula from B2,B3 and C2,C3.
I wish to have one conditional format apply to K and L. Presently it's L only where it works.
=OR(K1="",AND(K1="No",L1<>""),AND(K1="OJ",L1=""))
Why when I change the range from =$L:$L to =$K:$L does it not apply to both?
I have a conditional formatting issue, using VBA.
My conditional formatting code works fine for one column; however, when I try to apply the conditional formatting to two columns, the code only applies to the first column listed. Additionally, I have two sets of "rules" that apply to the same column. One of the "rules" includes two columns. The other rule applies to only one column. I'm not sure if this is my issue, or whether I'm trying to apply the same code to two different columns.
My code is below.
Code that applies to column "M" only...
I have ammended the colours to 4 colours that I need for a risk assessment tool . I have added a second worksheet and put a dummy sample of my table there. Can you help me with how to put two colums of conditional formatting in the sheet? The risk rating before and that after the risk treatment? Many thanks in anticipation. I have highlighted in yellow the columns where the product of the consequence and likelihood is likely to be and this is where the colour coding needs to be applied. if you can help with putting the solution in the spread sheet that would be a great help. Any reply via this forum or via my email would be greatly appreciated.
I am new and I hope I have not made a mistake in starting a new thread. Sorry if I have but I wanted to bring this up as a new post so I get a sooner response.
Is it possible to use conditional formatting to set all the unused columns (S - ZZ) interior color to gray?
View 5 Replies View RelatedI have what I think is a pretty complicated issue. At my job, we have a running and constantly changing list of jobs that we have to work on throughout the day. The job management system is really unwieldy and it’s often hard to tell what exactly we have to do today and what can wait until tomorrow. So a few times a day, I export the list to an excel doc, which I’m trying to set up with conditional formatting to highlight as green the stuff for today, ghost back what can wait, and leave white and blank the cells that don’t have any info in them. I but the attachment system wasn't working for me, so I can't provide the example I've been working on.
So far, I’ve managed to set up a formula in the conditional formatting that takes into account column C, but I need to take into account information in columns B, C, and D to determine if the job line should be highlighted or dimmed. Column B is the job's title and it is just text, column C is the Begin date, and column D is the due date. here’s what I need the conditional formatting to do.
If the begin date in column C is Today at 9:00am or earlier, and the due date in column D is today, I need to highlight the column. I managed to figure out the first part of this formula, but don’t know how to keep the line from hilighting if the due date is tomorrow: =$C2<=TODAY()+TIME(9,0,0)
The second part is that I need to hilight jobs that begin and are due today (have today’s date in columns C and D), regardless of what time they have if the task title in column B starts with “Rework Request from PS” or “Rework Request from QA"
Ideally, I’d like to change the text color to something nearly white to sort of hide the jobs that don’t need to be done today, and I figured that part out based on the formula I did manage: =$C2>=TODAY()+TIME(9,0,1)
apply this to the whole sheet so I can paste the report into it a few times a day without having to rewrite the formulas in a new sheet every time, and the number of items does change throughout the day. However when I do, the empty cells turn green as if they’re due today, and I can’t figure out how make them stay blank.
Date formatting. I have two columns first column is for the date (3/6/2013) and the second column is for the time (12:37:16 AM). I would like to combine the content of both columns to be like (2/11/2013 12:35:00 AM) .
I am using concatenate but I don't have any luck. How to combine the content of my two columns.
I'd like to compare two columns of data and highlight where a value doesn't occurr in both columns. The problem I have is that one of the columns uses * around the text to ensure it catches all variations of the value.
I have the following formula which would work if the data was exactly the same.
=COUNTIF(lst2,value)=0
However, these *'s are meaning it never works.
Is there a way to make it so that if the values with the *'s are not found in the 1st column of data, conditional formatting is activated?
Well I spent some time yesterday trying to figure this one out. It was only last week that I have taught myself how to use VBA in excel. However this looks like a though one.
Simply what i would like to do is highlight a column of a certain date (till a certain row number) based on a list of dates.
eg. Across the top I have a set of dates and on another sheet i have a list of dates.
I understand that there is a VBA code for such conditional formatting but i can't get my head around it.
I have to copy the formatting from "rows G&H" for 40,000+ lines. Is there an easier way than doing the formatting then highlighting the first two cells (G4 and H4) then dragging the bottom right hand corner down 40,000 lines?
View 5 Replies View RelatedI have a worksheet called "Online Post" and I need a way to do the following.
Check for two blank rows next to each other that have :a background and border, if true then do a Edit -> Clear -> All
So when the file is exported to HTML it doesn't include the blank rows.
I thought about using a marco that just deletes all the blank rows but I have blank row splitting up sections of the page.
I have a spreadsheet with 10 columns and 8000 rows. column 2 contains names and 3 contains districts of residence.
I need to highlight (or delete) whole rows of duplicate entries in the name column(2) that are in the same district (column 3).
Traditional conditional formatting highlights all duplicates. I just need those duplicates in column 2 that have the same text in column 3.
I have rows of information linked to other sheets with forms to fill out (master sheet). I want to be able to have a red fill if one of the cells is blank, which I do have working now, but I want a clear row if the "indicator cell" is blank.
View 1 Replies View RelatedInstructions are in the attached excel as well. Please see attached
If a person puts in a price in column A, that results in column B "Everyday Price Per Unit" being greater than column C "NDP", then highlight column A cell input red.
=Column B>Column C, then turn input cells in Column A Red.
First four highlighted are examples of thise formula scenario
** All based off an input into column
How to use conditional formatting to do so???
Excel Conditional Formatting 06.24.14.xlsx
conditional formatting in Excel. I have two columns with pertinent information. I need to know the following and format accordingly:
1. Is the number in column A positive or negative?
2. Is the number in column B less than 0.05 or 0.10?
I would then like Column C to just be highlighted a certain color depending on the combination... there are 5 possible combinations and I would like the cells to be formatted so that:
1. Positive and less than 0.05 - Bright yellow
2. Positive and less than 0.10 - Pale yellow
3. Negative and less than 0.05 - Bright green
4. Negative and less than 0.10 - Pale green
5. This "combo" just means the criteria wasn't meant... which is possible b/c sometimes Column A may have text instead of a number of b/c the number in column B is not less than 0.10. If either of these is true, I want the cell to remain blank.