In the first colum there are sub-columns that i need to reorder into flat format. This would be easy if all sub-columns were from the same size, but this isn't the case.
I think that I need to use a macro that finds the records on the colum, based on each title (A, B, C, D for the illustrative example attached). and then paste them into a new column.
I figure the before/after image would be best to describe what I need. I have various sections of data saved in this one spreadsheet which a program reads from for comparisons.
Right now they are in a list form like this:
Now the end result I want to look like this. Basically take the items in the column, and add the items selected next to it, one in each column not including itself of course.
These are basically the comparables, example this list would be "car manufacturers", and below that in cell A7 I might have another list started that has to do with "motorcycle manufacturers" etc
The end result would give a row of all possible combinations. The app we use pulls from the rows so it needs to have all of the items that are comparable in there and I can't seem to find a way in which to do this.
Hopefully this is understandable when you see what I manually did (unfortunately I need to do this for about 4000 entries, a few hundred groups of different characteristics).
VB:
Sub CreateFlat()
Dim wsData As Worksheet
Dim wsNew As Worksheet
[Code].....
In 2006 posting, this code was presented to the Forum. It works perfectly for a one dimension Crosstab. I have 4 dimensions that I need copied to the output.
I have attempted to modify the code, but nothing appears to adjust the pull of more than the first column of dimension data.
see the attached spreadsheet for the Crosstab data and desired output.
Attachment 49027I have an excel data dump of the organizational hierarchy out of ERP system that I need to convert to a flat file pivot table ready format.
The catch is that my lowest level always has to be a department meaning that a dept should be a last column however in the ERP not all the departments are mapped to the lowest level layer i.e. I might have Layer 1, Layer 2 dept or Layer 1 Layer 2 Layer 3 and then dept mapped to 3. Attached is the sample of the file.
I receive a flat text file every week which I would like to grab with excel and extract only the data I need and enter the data into separate cells and loop until I reach the end of the flat file. I got a subroutine written that allows me to open my text file and it will enter all the data however I need to know how to parse only the stuff I need and enter it into the right cells and loop until I reach the end of my text file. Here is what I have so far:
Sub testFSNew()
Dim fs As Object ' scripting.filesystemobject
Dim txtIn As Object ' scripting.textstream
Dim strFile As String 'File Name
Dim strLine As String 'Current line being read.
[code].....
Now so far this opens the text file and dumps all the data into an excel spreadsheet however when I say all I mean it dumps everything into the first cell and does not separate it, the following is an example of the text in the flat file. I will only put in the first 5 rows because their is 5000 rows in the real file.
HDR20120710
001010000366175270012008085197804171984102919730621DOE BJ52702B25713700000000016005
00101000036617JOHN 109080 55512345671978093000000001MACHINE REPAIR 4
001010000997885270002010384198910301989103019891030SMITH DS52501C257077S0000000000005
00101000099788ROBERT 109109 55523456781999082700000001ELECTICIAN-PROJECT COORD 4
Ok so the first problem is I don't need the first line it's a header line and if you will notice everyline of the file ends with either a 5 or a 4 but it is information about each employee, so the next line would end in a 5 and that would be the beginning of the next employee.
P.S. I noticed in the preview post that this message board truncated my flat file data, so keep in mind that each line is indeed 1 line ending in either 5 or a 4
In the attached spreadsheet my data consists of columns A through K. Essentially I want to line up the data so Column E matches column K. When no data exists in the other section, then a blank line should be inserted. The example I gave has the data formatted the way I want it. As you can see, line 11 is blank. Column L can be ignored as my manual way to handle this was to create an if statement that returned "Error" when something didn't match.
My spreadsheet has 29,000 lines in it. So doing this with my IF statement method would take enormous amounts of time.
Sample Problem.xlsx‎
A table re-organisation issue I have. Best described with an example I think:
Header
ID1
ID2
Q1
Q2
Q3
Q4
Data
1
8
A1
A2
A3
A4
[code]....
For the original data row (at the top) there are always 2 'ID' columns and 4 data columns.
There are about 100 rows, so the resulting table will be roughly 400 lines long.
I'm on the verge of doing this manually as the formulas and scrappy VB have so far been unsuccessful.
_A_______B______C___
1-Jan___13:00___13:40
1-Jan___13:10___13:50
1-Jan___13:10___14:00
2-Jan___16:00___16:35
2-Jan___16:20___16:40
3-Jan___10:25___11:20
Is it possible to run something so that either somewhere else on this sheet or on another sheet that I can get the number rearranged like this.....
_A_______B_______C______D_______E______F
1-Jan___________2-Jan___________3-Jan
13:00___13:40___16:00___16:35___10:25___11:20
13:10___13:50___16:20___16:40
13:10___14:00
And do it as the infrmation is entered into the original form above. The number of entries for each date will vary.
-- I have one sheet with 100 columns. (Master)
-- I have a report that only needs 10 of these columns, in a certain order. (Report)
What is the most efficient way to do this? Note I am not a VBA guru.
Can I add the column headers and positions (in Master) to an array?
Then in a new sheet (Report), copy and paste the desired columns.
I've got a lot of data like this. They are paired letter/number and I want to keep it like that.
a202b520t630s630d689
a265s687b953t953d1062
a47t547b890s890b1015
However I want to re order them in pairs so that its alphabetical eg. a 202 b 520 d 689 s 630 t 630
I need some VBA help in copying ranges from one sheet to another and pasting them on a predifined order. I have one worksheet that contains a large number of ranges with data on alphabetical order. Each entry on this sheet contains several rows of data and is identified by a name (e.g. entry "David" = rows 6:11, "Mark" = rows 13:18 and "Rose" = rows 20:25). I need to select certain ranges from this sheet and copy them on a predefined order on a different sheet (e.g 1) Rose, 2) David 3) Mark. The sequence is neither set by date, alphabetical order, nor by value. I have the order already predifined. I guess I could do this by specifiying the source sheet ranges and the destination ranges as well, but as I have over 2000 entries (each consisting of six rows), I need an efficient way to do it. Any help you could provide would be greatly appreciated.
View 9 Replies View RelatedI have a Pivot Table on which I am unable to drag the row items (Salesperson Names) to a different position. What am I doing wrong? Attached Sample
View 9 Replies View RelatedI have a workbook containing many pivot tables. When I create a pivot table everything works fine. When I close the workbook, reopen it, and refresh the pivot table, the new data will appear at the bottom of the table instead of being inserted in the proper row in the table. Since the table is no longer in sequential order my VLOOKUP functions don't work properly. What do I need to do to get the already established pivot table to refresh properly?
View 6 Replies View RelatedNothing else?
What settings do I need to change as it's handy to amend recorded code rather than write it all out.
I have a data entry sheet for attendance that shows the individuals name, date and hours attended. Each individual attendance is addedd to a new row. I need to transpose this data and display it in a report table, with the names in column A and showing hours worked for each day of that week. The formula needs to accomodate the growing data entry sheet as records are added (I'm using a date picker to change the week in the report sheet).
I've tried match, lookup and index and have confused myself thoroughly! Below is a sample of the data entry sheet (on left) and report table (on right)
******** ******************** ************************************************************************>Microsoft Excel - Book1___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutH1I1J1K1L1M1=
ABCDEFGHIJKLM1SurnameDateHours**Surname1-Jan2-Jan3-Jan4-Jan5-Jan6-Jan7-Jan2Smith1-Jan-076**Smith63600363Jones1-Jan-076**Jones66000304Rogers1-Jan-076**Rogers60600005Hathaway1-Jan-076**Hathaway60600006Pearson1-Jan-076**Pearson60000007Wills1-Jan-076**Wills60000008Smith2-Jan-073**********9Jones2-Jan-076**********10Smith3-Jan-076**********11Rogers3-Jan-076**********12Hathaway3-Jan-076**********13Smith6-Jan-073**********14Rogers6-Jan-073**********15Rogers7-Jan-076**********16Rogers8-Jan-076**********17Rogers5-Feb-076**********18Smith6-Feb-076**********19Jones6-Feb-076**********20Rogers
I have a column of cells with values - 0.2%, 0.32%, 0.22, 0.5 etc. The cells with % symbol are in ' Percentage, 2 decimal' format while the plain numbers are in 'General' format i.e. column contains cells in either of these formats. I need a macro where I can specify the column and it will select the cells with the % format, convert it to 'General' and multiply the result by 100 eg. 0.2% converted to 'General' becomes 0.002. When multiplied by 100, result is 0.2 i.e. is displayed without the % symbol.
View 7 Replies View RelatedI'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.
Trying to compare a list of names in 2 columns. Column A has a short list of names I'm looking for in the long list of Column B.
How can you create the macro that does...
If any value in Column A matches any value in Column B
Then format (bold, highlight, etc...)
I'm trying to sort out a list of names from a website that publishes names in the following format:
DOE John
VAN GOGH Vincent
DA VINCI Leonardo
NADAL PARERA Rafael
JIMENEZ RODRIGUEZ Miguel Angel
What I'd like to do is get the names in the following format
John Doe
Vincent Van Gogh
Leonardo Da Vinci
Rafael Nadal Parera
Miguel Angel Jimenez Rodriguez
Basically all the last names - which are all capitalized - would be moved to the end of the text string. Of course any leading spaces should be removed and I guess using the Proper() function, all capitalized words could be capitalized in a standard way.
I found the following function, here: [URL] ...
but what it does is just take the capitalized words and separate them into a separate cell, which is not all of what I want.
See attached picture. In column I need A B C D… replace by 1 2 3 4…
View 2 Replies View RelatedI have a simple sheet that will require some names in a column. Whenever I try to add a name I get an error message saying " it must be a date greater than or equal to 1/2/1904". Obviously I have formatted this column incorrectly, I have tried to highlight the column, right click Format Cells, text but no luck.
View 7 Replies View RelatedI found this board with a question for a column change to a specific date-format. This is normally very easy but in this case not for me.
The dates that are in the colums are in different shape!
Here are some examples:
21-Jun-07 16:00 A ( where the A is set by another program )
20-6-2007 11:00
18-Jun-07 08:00* ( where the * is set by another program )
The format I want is:
21-Jun-07 ( nothing more / nothing less )
+1+ ID + EventDate + EventType +
+2+ 111 + 1/1/1995 + Visit +
+3+ 111 + 1/30/1995 + Vaccine +
+4+ 111 + 4/22/1998 + Visit +
+5+ 222 + 7/7/2000 + Survey +
+6+ 333 + 8/8/2001 + Labs +
+7+ 333 + 8/22/07 + Pharmacy +
+8+ 333 + 8/22/07 + Visit +
shade the rows with alternating colors so that all the data pertaining to a give ID can be easily spotted. In the example above, I would need A2:C4 to be shaded one color, A5:C5 another color, and A6:C8 the first color again.
Is it possible to add a format to a column in a userform listbox. I need a numberformat in the first column in the listbox. The format is 000-000-00.
View 3 Replies View RelatedThis portion of my code should format my worksheets, add a total in column D and then format that total. It runs just fine through creating the total, but when I try to format it, something goes wrong.
Dim wsheet As Worksheet
Dim Last_Row As Long
For Each wsheet In Sheets
If wsheet. Name <> "Sub Query" And wsheet.Name <> "LDSUBREC (Voucher Query)" And wsheet.Name <> "Formatting - Deltek" Then
Sheets("Formatting - Deltek").Rows("1:5").Copy
wsheet.Rows(1).Insert
wsheet.Columns("A:G").ColumnWidth = 17
wsheet.Columns("H:K").ColumnWidth = 10
wsheet.Columns("L:N").ColumnWidth = 7
wsheet.Columns("O:O").ColumnWidth = 13.....................
I have a spreadsheet with 4 voltage value series (Bus voltage, two capacitor bank voltages, and a meter voltage). My issue is that I would like the markers on the chart for the capacitor bank voltages to be formatted according to another open/closed column of the capacitor banks. I would like the markers to be hollow if the capacitor bank is open and filled is the capacitor bank is closed. Is this possible without using VBA?
View 5 Replies View RelatedI have a column of numbers that I need to reformat. I created a macro to copy/paste special/transpose to get the data into separate columns. I then used & to get them into one cell. I can only have five sets of # in each cell. The macro works but it replaces the previous set of accounts. Is this the best way to reformat these numbers? If so, how do I correct the macro so that it doesn't replace the data i already reformatted?
I have attached a sample spreadsheet.
I have the following code. I am trying to conditional format a row based on the value of the A column of the row.I have a total of 3 rows. I have to use VBA because in time I may have 4 rows of data. The problem is I after the first do while loop the code stops and the code doesn't proceed to format row 2.
View 2 Replies View RelatedI am working with two tables in one sheet. The first table requires seven columns. Under this table, i want to have another table that has 14 columns right under it so that their widths are the same. Is this possible? It would be like having two sheets in one.
View 2 Replies View RelatedIn the same worksheet, I would like to format column "U" based on the alpha character in column "T" and also format column "AA" based on the alpha character in column "Z". There are three alpha characters that will be used in columns "T" and "Z" ( upper case and lower case), "A" with the format of $ #,000 & "H" with the format of $ 0.00 & "P" with the format of percent %. I want to format column "U" & "AA" prior to entering a value.
Column "T" & "Z" are a pull down menus containing only the values listed above (a,A,h,H,p,P)
I had asked and received an answer regarding the above pertaining to columns "A" and "B" and was given an answer that works, however, today my boss changed the configuration of the spreadsheet and for the life of me, I could not get the code to work using the new parameters.
