how do I transform thousands of groups of columns (each group has 6 columns) into A Single row (placed into another sheet) using Macro?
Since my data is large, so efficiency is also important.
I have a spreadsheet which has data in the format of
Name
Address
City
State
Zip
Phone
This appears in the form of rows. How do I make the data in a table meaning,,,
Name Address City State Zip Phone.
My production spreadsheet has thousands of unwanted columns and I am unsure as to how they appeared. I want to delete them. I have tried selecting all of them and deleting them, but nothing happens. I have tried go to > blanks, but it freezes because there are so many columns.
Also, I tried to copy paste to a new sheet, but the formulas get messed up.
For a mental picture, the spreadsheet is approx 225 rows by 25 columns.
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.
View 4 Replies View RelatedIn a workbook some of the numbers I have to use are in format: 12,50. How can I modify the formula so it transforms it to 12.50 (so that excel knows this is a number)? I am getting the values with:
View 7 Replies View RelatedI need to transform many text (that Looks like numbers) in to numbers. The thing is that, there is an option to search for errors in the "formulas" menu, but I want a code in vba that I can run.
so in conclusion the thing is:
1. look for text that "looks like" numbers
2. turn them into numbers
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?.
View 4 Replies View RelatedI 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
[Code] ........
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
Transformed to:
0>>> -23 -12 0 12
>>>> 700 701 698 699
100>>-50 -25 0 12
>>>>>670 688 600 700
i have a data as below
abc1
def2
ghi3
jkl4
and i need the data as below in one column
a
b
c
1
d
e
f
2
g
h
i
3
...........
needed in this regard either macro or formula
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
[Code] ........
Table 2: How I want the data to be transformed
Account number
Account Name
Description
Month
Amount
4010
Bank
Sales
2013-02
10
[Code] ........
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
example: |14|23|3|0|45|0|10| => |2|3|1|0|4|0|2|
Would it be possible to do with Excel?
I would like to know if there's an easy way to transform an excel file from 2000 version to 2007 version or if I have to redo completely my file
View 9 Replies View RelatedI 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.
One of my colleagues wants Excel only to show whole thousands, but I am not sure how to do this
For example, if the number is 78654.98, he only wants it to show 78.
Is this possible?
On my indows XP box, when I type a number into an Excel spreadsheet and it is in "General" format, the numbers appears like this (examples):
1234567
or this:
7.125
or this:
8.1
I want them to look just like that except for the first one, which I want to have the thousands separator:
1,234,567
I don't want to use a number format because
Excel makes you set a specific number of decimal places...I want the decimal places to float depending on how many decimal places there are in the entered number (just like the "General" format does). So basically, I want a "General" format, but with thousands separators (commas).
I tried using something like ###,###.### but that leaves a decimal place at the end of each integer (i.e. "123,456.") which is also not acceptable.
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.
[Code] .....
I am writing some code for a fast vlookup. I have the following template. The formulas in column AU and AX need to be automated. actual file has 170000 rows, so the vba code needs to be fast.
AU formula is: =VLOOKUP(A2,$BA$2:$BC$180000,3,FALSE)
AX formula is: = IF(AN2 = "Test1","To be adjusted",
IF(OR(AN2="Test2",AN2="Test3"),VLOOKUP(A2,$BE$2:$BF$180000,2,FALSE),
VLOOKUP(A2,$BA$2:$BC$180000,2,FALSE)))
I'm trying to sum selected columns of a specific row in a spreadsheet. The row name is to be matched to the same name in a different sheet; the column headings are also found on that separate sheet. I've tried SUM using INDEX and MATCH, but it's not working (most recently I get #N/A). I'm trying to do this sum for each separate row, first through sixth, with varying columns for each row.
I'm not opposed to getting into VBA, but wanted to see if there was something that I missed with the regular formulas.
My most recent iteration is:
=SUM(INDEX('Current'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($B6,'Current'!$A$1:$A$16,FALSE)):INDEX('Curre nt'!$A$1:$G$16,MATCH($A6,'Current'!$A$1:$A$7,FALSE),MATCH($C$2,'Current'!$A$1:$A$16,FALSE)))
[Code] .........
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)
Basically I want to be able to type a number and have a colon inserted two spaces to the left. So 123 would become 1:23, 1234 would be 12:34 and 12345 would be 123:45.
I'm working with anywhere from one to several thousand hours in a spreadsheet. I'd like to be able to use a time mask like this:
i want to know how to numberformat numbers (Currency) into thousands,lakhs.
For example i m having amount in colmun D like 1239.00 i want to format the numbers in this column as Currency with thousand seperator and lakh seperator. like1,239.00 or 1,23,900.0
In our language the thousand groups between numbers are separated with a space, like 2 345 345. (so we do not use , or . ) How can I do it in a macro, is there any possibility?
How should I change this: MERGEFIELD brutto # ### # ## ### Ft.
(I know this is without braces, I cant copy them). so this gives me a result 2345678 Ft. and I want 2 345 678 Ft.
I'm using the record macro function to make a code that is concerned with formatting information generated in excel by another software program.
My original idea was to replace the information in column F with the concatenated info of column F-L, but I couldn't get this to work either. So now I'm looking for a work around for my work around.
I can get the macro to work just fine with "Macro 1" below, but unfortunately when I use the entire column range, it populates thousands and thousands of rows below the (around 50) lines of information I have. However, the populated information in my sheet is completely variable - it could be 1 line or 5000 lines. These will also be printed, so I cannot just cap the range at 5000 because then it would print all of the blanks.
I tried several work-arounds after googling. I tried to leave the blanks and then delete them once they were populated - messy, and it crashed excel. I tried End(xlUp) and I tried using lngLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
how to only copy the formula in Column M to the last row of information (which would be the row with the last value in Column A).
MACRO 1:
Columns("M:M").Select
Selection.FormulaR1C1 = _
"=CONCATENATE(RC6, "" "", RC7, "" "", RC8, "" "", RC9, "" "", RC10, "" "", RC11, "" "", RC12)"
Range("M:M").End(xlDown).Select
Columns("M:M").Select
Selection.Copy
Columns("F:F").Select
[code].....
MACRO 2 (Only the Changes):
ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Select
Range("M" & lngLastRow).Select
Selection.FormulaR1C1 = _
"=CONCATENATE(RC6, "" "", RC7, "" "", RC8, "" "", RC9, "" "", RC10, "" "", RC11, "" "", RC12)"
Range("M" & lngLastRow).Select
Selection.Copy
I need to convert thousands of files from .csv to .xls format. Filenames are numbers (1.csv ... 20000.csv). Filenames do not change.
Could just open, Save As, and Close.
Is there a simple way to run a macro once and convert everything? I don't know how to code the macro to open one file, convert, then close, then open the next...
We have created a userform with several textbox for numbers. We'd like to show them with a format similar to "###,##0.00" and allow the users to enter numbers without commas, but always show the textbox formated. Is there any special way to manage this format? As the textbox is a "text" value, how can we manage the input and the numbers? Should we always replace the "," withi a nullstring? Example:
Private Sub tbCakPrice_Exit(ByVal Cancel As MSForms.ReturnBoolean)
tbCakPrice.Text = Format(Val(tbCakPrice.Value), "######0.00")
tbPxCakCnFCgl.Value = Format(Val(tbCakPrice.Value) + Val(tbFleteRealCgl.Value), "########0.00")
End Sub.......................
Before
XYZ1
XYZ2
XYZ3
XYZ4
XYZ5
XYZ6
XYZ7
After
XXXXXXXYYYYYYYZZZZZZZ1234567
[Code] .....
I have 100 columns in 1 worksheet, each column has 200 rows, I want to combine all these columns into a single column.
Therefore, Column 101 or a new worksheet Column 1, will have 20000 names.
