there is a ONE column containing data. it looks like 7 rows of content, 2 blanks, 7 rows of contant, 3 blanks etc. there can be more than 1500 rows in this document.
i am looking for a macro which would copy these 7 VERTICAL rows in 1 column and transform them into a NEW HORIZONTAL ROW IN 7 COLUMNS.
step by step it would be like: - copy 7 rows in 1 column - transform it into 1 row, 7 colums and put it ONE ROW ABOVE THE ORIGINAL - delete original 7 rows in 1 column
i did macro by recording the whole action but it works only for first repeat. it does not work then because the macro keeps the row number and rewrites the content. i would need a macro which works independently of the row number.
Is there a ATransform or replace command in Excel. i have a value of 04/23/09 which is a date and would like to replace all "/" with "-' thereby making the value 04-23-09. I could examine each character value within a cell but this would just make a long coding and would like to know if there's a shorter way to do this?.
I have a spreadsheet of multiple entries per person. I need to calculate the total per person and have only one entry per person of different character variables within one column. For example:
Jon Smith Fair
Jon Smith Assignment
Jon Smith Fair
Nancy Drew Info
Nancy Drew Info
Rachael Smith Notes
Turned into this:
Name Fair Assignment Info Notes
Jon Smith 2 1 0 0
I can already calculate a 1 or 0 for each row with =SUM(IF(A2:A3442=A2, IF(D2="Fair"))). Which creates a 1 or 0 in each row labeled for each entry but I do not know how to create a new spreadsheet with just one entry per person with the totals for each column.
I need a macro that will transform numbers in Column A to rows. i.e. The (6) astericks will determine the start of the next row. Transformed to: ****** 0 -23 700 -12 701 0 698 12 699 ****** 100 -50 670 -25 688 0 600 12 700
How can I transform a text string into a formula. ='F1'!$C$2
This appears as a text and I want to convert it to a formula exactly like it's written. I made this formula like this because I need to get the value from 1000 sheets. So I use the formula to change the sheet number automatically.
But now I can not convert the text into a real formula.
I know that I could do it with a macro but I prefer to do it without using a macro.
I have a table with data and I want to transform the data in table 1 to the format in table 2 with a formula. I want to change the table format since I have data with 12 000 rows in table 2 format. I haven't been able to find any formula that works, so I will try to explain my problem
Table 1: How the data looks like
Account number Account Name Description 2013-01 2013-02 2013-03 2013-04 2013-05
4010 Bank Sales 0 10 0 5 0
Table 2: How I want the data to be transformed
Account number Account Name Description Month Amount
4010 Bank Sales 2013-02 10
1. The same accounts are used several times, but with different description or months. 2. All "0" values are eliminated in table 2.
My first thought was to use a pivot table, but I can't use it on sheet 1 since each month become a separate value.
Maybe there are some setting in pivot tables that makes it work, but I would prefer a formula to solve the problem.
I have a series of raws with 7 numbers in each raw; sums may vary a lot from raw to raw, and also differences between one number and the other in a same raw.
Now, i need to obtain a much simpler distribution of scores, so reducing differences.
I would need to transform all numbers into a 0-4 score distribution, according to differences between numbers in a same raw. It's like a kind of rank score.
I want to obtain in each raw:
- 1-2 numbers with score = 4 (if three or more numbers have similar values: no 4 scores) - a maximum of 3-4 numbers with score 2-3 (according to differences between these numbers but also also according to the 4 scores already assigned) - all other scores = 1 if the numbers are >0 - all other scores = 0 if the numbers are 0
I have a sheet with data imported of another software. In the column "G" are all the dates, and the problem is how can i change the format, because, some of them are "mm/dd/yyyy" and the rest are "mm/dd/yyyy".
Is there a way to change it to the same format? maybe there are some dates like "11/02/2007" that are "02/11/2007" (i don't know how change it with code), and others that the difference between formats is logically clear. I want to get the format "dd/mm/yyyy".
After this i want to apply a filter between two dates, for example, (in dd/mm/yyyy) from01/05/2007 to 30/08/2007.
I've tried different ways, but i'm not able to transform the format of the dates to the same format, although i've read your help and other posts that talk about this.
I want my macro to transform the cells whom values are visible into pure value no formula "behind them". I have simple multiplication formulas in those cells. If The result of a multiplication is 0 and I format that cells to not show me that 0 with "0.000;-0.000;;@" the macro still deletes the formula from that cell.
I have never written a macro and when I record one I usually have trouble with the relative references.
I am trying to write a macro to transform formulas into values every month.
I want to transform formulas:
- across a range of tabs: each tab is exactly alike and is named page-1 to page 25 - on a different column every month (same column across all tabs) - on the same rows: L168 to L227 and L266 to L277 (same rows across all tabs)
Am working with a spreadsheet of about 1000 rows and 50 columns which I need to transform into 2 columns and as many rows as needed. I have attached a simplified example of a 3x3 spreadsheet so you can see what I mean. I have already managed to get my result using Filtering and some copy and paste but I am struggling to now fully automate the solution.