Aligning Rows Of Data So All Of Same Value Is In Same Column?
Apr 23, 2013
I have a data set with raw data from an online survey with >2000 rows of data, with each row varying in width from 2 columns to 4 columns wide of data, located in columns A through D. Each row has the value 2 in it as well as some additional values on either the right or left side (the location of the value 2 varies -- so it's not always in the same column). Picture attached of some example data for clarification:
Picture 1.png
I want to figure out if there is a function that can align all of the 2's in the same column -- let's say Column C -- while maintaining the same data that was to the right or left of the 2 (up to two values on either side) before all of the 2's were aligned in the same column.
I could certainly manually align the central column by copying and pasting repeatedly, but that would take an incredibly long time -- and I am sure that there is a better way to do this. I can use a VLOOKUP function to align all of the 2's in a column as well as everything to the right, but I have tried using the INDEX and MATCH functions to do a VLOOKUP to the left, but this doesn't seem to be working because the source column (where the number 2 is) varies by row, so it doesn't match in the same way that a VLOOKUP function does.
View 5 Replies
ADVERTISEMENT
Dec 22, 2008
I have what I believe to be a simple problem, but for the life of me, i can't seem to figure it out.
I have a list companies in column A that have a corresponding revenue number in column B.
In column C, I have ANOTHER list of companies and their corresponding revenue number in column D.
Example:...
View 14 Replies
View Related
Nov 10, 2011
I have a set of 50 or so large worksheets that need updating each month. My problem is the figures I need to update these sheets with are formatted differently (different alignment of blank rows) to the destination.
I'll try to illustrate, this is the sheet I need to add data to on a monthly basis... (you'll need to imagine the numbers in column form as I'm no HTML expert)
May June July
23 34 45
65 34 45
34 23 23
[Code] .......
The blank rows need to remain uniform. When I need to paste in August's data the blank rows in the raw data extract are aligned differently and it's a laborious job deleting and inserting cells to get them to align (the sheet has hundreds of rows).
I'm capable enough of recording a macro to replicate the delting/inserting of cells BUT of the 50 or so sheets I need to update there's no uniformity of where the blank rows occur. To further exacerbate, there's no uniformity over where the blank rows occur in the data that needs to be pasted in, either. The blank rows already in the worksheets HAVE to remain and can't be deleted or altered.
View 9 Replies
View Related
Aug 13, 2014
I've got 3 pairs of columns and I need to sort through them and align the cells in columns E&F with those in A&B and C&D. The cells I need to match up are the times (columns A, C and E)
Example - convert this:
A...............................B..........C...............................D.........E...............................F......
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:56.....6.14.....30/07/2014 14:22:56.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
Into this:
BID TIME.....................BID.......ASK TIME....................ASK......TRADE TIME................TRADE
30/07/2014 14:21:04.....6.10.................................................30/07/2014 14:21:04.....6.13
30/07/2014 14:21:06.....6.11........................................................................................
30/07/2014 14:22:37.....6.11.....30/07/2014 14:22:37.....6.13.....30/07/2014 14:22:37.....6.13
30/07/2014 14:22:54.....6.11.....30/07/2014 14:22:54.....6.13.....30/07/2014 14:22:54.....6.13
30/07/2014 14:22:56.....6.11.....30/07/2014 14:22:56.....6.13.....30/07/2014 14:22:56.....6.13
............................................30/07/2014 14:22:56.....6.14............................................
............................................30/07/2014 14:22:59.....6.13.....30/07/2014 14:22:59.....6.13
I don't know VBA so hopefully there's a way of doing this with a basic Excel function.
View 2 Replies
View Related
Feb 11, 2013
How 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?
View 2 Replies
View Related
Oct 23, 2008
I run reports from Accounting systems that reflect different periods. Of course each period ends up having more or less accounts. See the attached file. This is just one little sample of big massive reports. I need to line up accounts across each Quarter. so that i can calculate differences between each quarter.
In the past few times, I have done this manually. There has got to be a way to do this quicker. I have tried Pivot table. but pivot table didn't help either (unless i was not doing it right).
View 3 Replies
View Related
Feb 22, 2007
How do I sort out columns aligning them to match data in another column?
Column B is 200 rows long all with data such as FLEZ054246. Columns C is 100 rows long but it will match some from B. I need to align C,D,E with B as long as C & B match. The rows that don't match can be left open for C, D, E.
EX.
Column B Column C Column D Column E
FLEZ054246..........TXEZ061244.........WCG................TX
TXEZ061411.........TXEZ059129..........DOUGLAS...........FL[code].....
View 8 Replies
View Related
Apr 30, 2013
aligning some data. Cupno has more entries than seqno and there are duplicate entries. I cannot think of a way to get seqno+results to align with cups. If it's possible to delete rows the don't have a corresponding seqno thats ok too. I've attached the example workbook. Sheet1 is the data and Sheet2 contains what ideally the results should look like
View 2 Replies
View Related
Dec 31, 2008
Column A contains the date, every day since 1/2/07 to present
Column B contains a value for that date
Column D has dates within the range in Column A but at odd days (e.g. 4/2/07, 10/2/07)
Column E has data which corresponds to the date in Column D.
Can anyone advise of a formula which can allow me to put the data in Column E in line with the dates on column A rather than the dates in column D?
I want to have in Column C values from Column E that match with the dates in Column A, so for example next to 1/2/07 the cell would be empty but next to 4/2/07 in column A would be the value corresponding with that date in columns D & E.
View 9 Replies
View Related
Feb 22, 2007
How do I sort out columns aligning them to match data in another column?
For instance.
Column B is 200 rows long all with data such as FLEZ054246. Columns C is 100 rows long but it will match some from B. I need to align C,D,E with B as long as C & B match. The rows that don't match can be left open for C, D, E.
EX.
Column B Column C Column D Column E
FLEZ054246..........TXEZ061244.........WCG................TX
TXEZ061411.........TXEZ059129..........DOUGLAS...........FL
TXEZ061244.........TXEZ061101..........ERNIE...............TX
TXEZ061101.........FLEZ059314..........JASON...............FL
FLEZ054336.........TXEZ064240.........ERNIE................FL
TXEZ063075........TXEZ059503.........MICHEAL............TX
FLEZ060652.........TXEZ059027.........CLAIRE...............TX
FLEZ-054341........TXEZ059063.........CLAIRE...............TX
TXEZ060723.........TXEZ059164.........PAUL..................FL
TXEZ059503
FLEZ059314
TXEZ059164
TXEZ059129
TXEZ059063
TXEZ059051
TXEZ059027
I need it too look like this:
Column B Column C Column D Column E
FLEZ054246..........FLEZ054246...........ERNIE................FL
TXEZ061411
TXEZ061244.........TXEZ061244.........WCG..................TX
TXEZ061101.........TXEZ061101.........ERNIE.................TX
FLEZ054336
TXEZ063075
FLEZ060652
FLEZ-054341
TXEZ060723
TXEZ059503.........TXEZ059503.........MICHEAL............TX
FLEZ059314..........FLEZ059314.........JASON...............FL
TXEZ059164.........TXEZ059164.........PAUL.................FL
TXEZ059129.........TXEZ059129.........DOUGLAS...........FL
TXEZ059063.........TXEZ059063.........CLAIRE..............TX
TXEZ059051
TXEZ059027.........TXEZ059027.........CLAIRE..............TX
View 6 Replies
View Related
Jan 18, 2014
I work on graphics which show financial data. The base is day data together with calculated added values the graphic worked and showed good pictures.
But now I encountered a problem with the graph - related to not listed days, points are "generated" which do not be in line with the rest of the data !?
EXCEL_Forum_20140118.jpg
View 1 Replies
View Related
Sep 27, 2013
I have data below that is misaligned. I would like to know if there is a simple way to automate it's alignment like below
Table:
PC HW
PC
Operating Income
PC MN HW
PC MN
PC
Operating Income
[code].....
View 1 Replies
View Related
May 12, 2014
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.
View 4 Replies
View Related
Feb 6, 2009
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.
View 14 Replies
View Related
Mar 2, 2011
I 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
View 7 Replies
View Related
Feb 15, 2007
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.
View 9 Replies
View Related
Feb 27, 2013
I have a spreadsheet with an example attached. There is data in multiple rows across multiple columns but not all rows and columns have info in every cell. I would like all the information to align to the far left column but stay in their respective columns.
View 7 Replies
View Related
Mar 20, 2014
My application is in vba and I want to log information in text file which required four columns. The tool should open the log file and log the issue and close the file immediately so that other processes running in parallel could also post the issues to it without loss of data. Keep appending to the log file.
Log file should look like: LogFile.txt
View 9 Replies
View Related
Nov 15, 2008
I want to bring in text files that result in a single column and then want to be able to align equal text in the column next to each other. I used to be able to do some moderate programming in FoxPro and used a transaction that was called set relationship to field+field, etc.
If there is no equal text in either of the other columns then I have tried to set it to a column by itself. Of course this comes data comes in all in one column with no spaces. I have searched the help files and the net and just can't find a way to do this. I'm thinking this might be attuned to Access more than Excel but I am not proficient in this. Right now I am doing a cut and paste to make this work but some of the imported text have upwards of 800 lines.
View 4 Replies
View Related
Jun 5, 2009
My question is about aligning text in ListBox columns. In a ListBox, I have 5 columns. I need each column's text to be aligned in its own way, independent of other columns. The following explains my need more specifically.
I need the text in column 1 to flush right against column 2, and for column 2 to flush left against column 1. I need column three centered. And I need the text in column 4 to flush right against column 5, and the text in column 5 to flush left against column 4.
View 3 Replies
View Related
Jul 2, 2013
Any way to vertically and horizontally center an autoshape in a cell without VBA. I created a shape and placed it in a cell but don't think it is centered. Also, if I add shapes to adjacent cells, I would want them all to be centered directly in the middle of the cells they are in. Using the align feature only seems to make sure the shapes are aligned to each other but doesn't seem to put them in the center of their respective cells.
View 1 Replies
View Related
Dec 11, 2006
Our Excel spreadsheet organizes names, addresses, and the amount of money per account in the following manner:
Column A Column B Column C ....Column X
Row 1:"Account Name" "Name of Company" "Money in Accout", etc.
Row 2: "Address of company"
Row 3: "PO box"
Row 4: "More information or State and Zip Code"
Row 5:"Account Name" "Name of Company" "Money in Accout", etc.
Where the "Name of COmpany", "PO Box", "More information or state and Zip code" are all located in Column B.
We have anywhere from twenty to a thousand or so names organized like this going down a spreadsheet. In addition, the number of rows underneath a particular Company varies.
So, I was wondering if anybody knows if a VBA program could be written where the code would move the cells that are put under the name of the company (i.e., Row 2, Column B; Row 3 Column B) so they'd appear only on row 1? Ideally, this program would know when to start putting the information on, for example, row 1 or row 5 because there is an account name in Column B.
View 9 Replies
View Related
Jul 24, 2014
Is there anyway of aligning part of a formula to a certain character limit (63)?
For example:
Aaaaaa(A1), bbbbbbb(B1), 1111111.00(C1)
Aaaa(A2), bbbb(B2), 11.00(C2)
to
Aaaaaa(A1), bbbbbbb(B1),____________1111111.00(C2)
Aaaa(A2), bbbb(B2),______________________11.00(C2)
View 1 Replies
View Related
Mar 17, 2012
I have 35 text boxes to add to the bottom of a chart. Is there no way to mark multiple boxes and align them to the left or right or make them all the same height?
View 2 Replies
View Related
Apr 20, 2014
I student data in the following format
STUD ID, Surname, Forename, Subject
E.g
0001,Smith, Stan,Biology
0001,Smith,Stan,Chemistry
0001,Smith,Stan,Physics
Etc
I would like to display it so instead of there being multiple rows per student there is just 1 row with each subject in a new column
e.g
STUD ID,Surname,Forename, Subject 1, Subject 2, Subject 3
e.g
0001,Smith,Stan, Biology, Chemistry, Physics
Is there any way I can do this quickly for hundreds of students?
View 12 Replies
View Related
Apr 30, 2014
How to do this ? keep a running record in rows...
A---H I J K
1 4-4 8 2 6
2 8-5 6 1 4
3 2-8 3 2 5
4 6
5
6 5
7 6
8 1
9 4
10
11 8
12 3
13 2
14 5
View 9 Replies
View Related
Feb 22, 2014
figure it out thus problem with macro,
before/sample data :
name
address
gender
activity
code
[Code]...
after macro :
george
g
t
jas
[Code]...
it's possible with macro??
View 3 Replies
View Related
Jan 9, 2008
I have data that is all in column A there are 8 pieces of information.
So client 1 info is listed from A2-A9, client 2 from A10-A17 etc. etc.
I set up A1-H1 to be the headers necessary.
How do I go about moving A2-A9 to A2-H2, then A10-A17 to A3-H3 etc. down through all the contacts which go down to A2400...
View 9 Replies
View Related
Apr 18, 2006
I'm trying to take spreadsheet data in columns and reformat into rows. See attachment. Basically i want to duplicate columns A thru E by row specific and then take columns F thru K in pairs to match up with the duplicated rows corresponding to the specific rows. So every row currently will become 3 rows (A thru E) with Columns F and G (old F & G row 1, H & I row 2, J & K row 3)
View 4 Replies
View Related
Nov 28, 2007
I'm in need of a macro that takes data from cells in column A and moves them into different cells. Each block of data includes a company name, contact name, address and usually (but not always) phone, fax, email, website, etc. I want to move each set of contact info into a set of cells that begin in column A, starting with company name. Refer to the attached spreadsheet
View 3 Replies
View Related
