I have two columns of data as follows:
10:57:42 273
10:57:42 263
10:57:42 253
10:57:42 241
10:57:37 273
10:57:37 243
10:57:37 249
10:57:37 261
10:57:37 253
11:04:47 241
11:04:47 253
11:04:47 263
10:54:31 254
10:54:31 240
10:54:31 265.......
I am stumped on how to transpose multiple columns to rows based on specific criteria. Here is an example of the data I am working with:
Acct #Rev CodeUnitsCharges10094537034503$0.0010094537034501$605.0010094537037101$0.0010096359034503$0.0010096359034501$355.0010096359037101$0.00
I want it to look like the following:
Acct #Rev CodeUnitsChargesRev CodeUnitsChargesRev CodeUnitsCharges10094537034503$0.004501$605.007101$0.0010096359034503$0.004501$355.007101$0.00
I should note that there is oftentimes more than three rows for the same account number, sometimes it could be as many as 20 rows for the same account.
I need to transpose a three column worksheet with thousands of rows containing repeats based on the value in Column A (between 2 and 11 consecutive repeats), into rows with no repeats, and the values from the repeated rows into new columns. Column A has a unique numeric value corresponding to the repeated rows. Column B has 1 of 11 values and Column C has 1 of 4 values.
The worksheet looks like this:
1 abc x
1 def y
2 ghi x
2 abc n
2 lmn x
2 def z
2 jkl y
I need to make it look like this:
1 abc x def y
2 ghi x abc n lmn x def z jkl y
I tried using the following code, but it dropped all the values from column C:
Sub kTest()
Dim a, i As Long, w(), k(), n As Long
Dim dic As Object, ws As Worksheet, s As String
Set dic = CreateObject("scripting.dictionary")
dic.comparemode = vbTextCompare
With Sheets("sheet1")
a = . Range("a2:b" & .Range("a" & Rows.Count).End(xlUp).Row)
End With
I am attaching a workbook " Book 1" that has the results from the above macro in the first worksheet "Final Report", the origninal data "orig data", and the format I need to get the data into "needed data".
I have following information. I need to transpose the columns into rows
C
10001
V
1000
V
1001
V
1002
C
10002
V
1001
V
1003
C
10003
V
1001
V
1003
V
1004
The expected result should be like
10001 1000 1001 1002
10002 1001 1003
10003 1001 1003 1004
I have a set of data which in one column, includes 500 countries, each followed by a list of 43 entries. I want to transpose the data into rows, so that the countries are all in one column, and the 43 entries are in rows as well..
View 7 Replies View RelatedHow to selectively transpose a row of dates to columns. I'm not sure exactly how to explain this, so below is an example of what the data look like entered into the spreadsheet:
study ID
provider
visit 1
visit 2
visit 3
visit 4
visit 5
[Code]....
I'd like to extract the data into a new table on another worksheet that looks like this:
Date
provider
study id
visit #
7/21/13
Test Name
10001
[Code]...
This is just a quick example, but basically it would continue through all possible visit dates for the first study ID, then move to the next row of data (i.e. the next study ID) and extract the data from the row and transpose it in the appropriate columns moving down...
How to convert multiple Rows recors to a single row record in a Notes(csv) format? Have update my xls file. My source is in the below format(Source.xls):
GroupName_A,Name_A
GroupName_A,Name_B
GroupName_A,Name_C
GroupName_B,Name_D
GroupName_B,Name_E
GroupName_B,Name_F
GroupName_B,Name_G
GroupName_B,Name_H
GroupName_B,Name_I
I want to convert it to a CSV file where by it can be import to Lotus Notes (output.xls):
1,1,Group,GroupName_A,"Name_A,Name_B,Name_C","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
1,1,Group,GroupName_B,"Name_D,Name_E,Name_F,Name_G,Name_H,Name_I","CN=John Sam/OU=FIN/OU=staff/O=IBM,CN=Mary Flow/OU=FIN/OU=staff/O=IBM",CN=John Sam/OU=FIN/OU=staff/O=IBM
As you can see only GroupNameN, and Name_N are varibles, the rest of the fields are static. note that there is opening and closing quota for column "E" and "F" in output.xls
I'm trying to get a macro together that will take a set of workbooks that I've merged (using Ron de Bruin's RDBMerge add-in) and transpose all columns from B to HB into rows. Now, I know that each spreadsheet is 210 columns and 244 rows large and they are concatenated on one another. Attached is a brief example of what I am trying to go from and what I am trying to get to.
View 1 Replies View RelatedI have the following macro which I use to transpose a number of columns in one sheet to rows in another sheet.
PHP
Sub task1()Dim i As Integer, n As LongFor i = 5 To Columns.Count Step 2 n = n + 1 With Sheets("Working_Checklist_1").Cells(10, i).Resize(16) Sheets("Summary_Intermediate").Cells(n + 2, "c").Resize(.Columns.Count, .Rows.Count) _ .FormulaArray = "=if(transpose(Working_Checklist_1!" & .Address & ")=0,"""",transpose(Working_Checklist_1!" & .Address & "))" End WithNextEnd Sub
I'd like to change this macro so to transpose from rows to columns. I've tried a couple of things, but can't quite get it to work.
I'd like to transpose every second cell starting from E7 to IV7 in Working_Checklist into column BU in Summary_Intermediate, starting from BU3.
i have the following spreadsheet with dummy data however, there is a before and after scenario i have posted is this possible with a macro ...
View 9 Replies View RelatedI need to find all rows that have columns that match in all 3 of the columns.
I then need to delete all but the last row in each "group" of rows.
like:
1 q w e
2 q w r
3 q w r
4 q w r
Delete rows 2+3
ertret.jpg
I am trying to delete rows that contain the same addresses BASED ON COLUMNS.
Do you see how in columns C and D they all match EXCEPT for row 4? How do I delete allllll the rows that when c and d match.
I want to keep the ones that DO NOT MATCH.
P.S I HAVE 4000 ROWS I WOULD HAVE TO GO THROUGH
Below is a table of data I am trying to sum. In the first column, is the account code, second column has the Hotel number code, third column is the description and the 4-6 columns have amounts for March, April and May.
I am trying to sum the data for March, April and May for account 107000 using this formula, but it is only picking up the first row.
=-IFERROR(SUM(OFFSET(PnL!$C$9,MATCH($A29,INDEX(PnL!$A$10:$A$500,0),0),1,1,ControlMonth)),0) to sum
FYI:
PnL!$c$9 = is the block referred to as C9 below
$A29 contains 107000
PnL$A$10 is the block below A9 below
ControlMonth is currently set to 3
[code].....
need a quick macro to match rows in two columns as per demonstartion below.
View 9 Replies View RelatedI would like to rows based on multiple column conditions criteria. ie., if the columns N, O, P values are "", then hide the particular row. The logic given in the website here, i tried But, it is not 100% working. It works for a few rows at the start of the database & it works for the rows at the end of the database. In between, for a few rows, even if the column values are "" it does not hide those rows.
View 2 Replies View RelatedI've tried using a Pivot Table, a Match Array V-Look Up formula I found on here (and can't seem to find anymore) that I altered to fit my cell needs, and even implemented RP-Excel.
This is an example of what I have:
Box
101
101
102
102
102
[Code]...
This is an example of what I'm trying to do:
Box
101
102
103
104
[Code]....
Additional challenges:
The actual workbook is over 4,000 rows long with a lot of various other information in neighboring columns.
The dates represent each time a specific box was filled. Some boxes only filled once, some up to 14 times. I only want up to the first five fills.
Able to try any of the following, or combinations of: Formulas, Pivot Tables, VBA, RP Excel
I need to Index & Match the Max Value for 3 separate columns labeled "Price". The columns are not adjacent and cannot be moved. I am able to get the Max for the 3 Price columns using:
=MAX($L$12:$L$45,$O$12:$O$45,$R$12:$R$45).
However, I need the matching value in column D labeled "Int. SF". I can only do this for one column at a time so far using this formula
=INDEX($D$12:$D$45,MATCH(MAX($L$12:$L$45),$L$12:$L$45,0)).
When I try to use all 3 columns with Index & Match
=INDEX($D$12:$D$45,MATCH(MAX($L$12:$L$45,$O$12:$O$45,$R$12:$R$45),$L$12:$L$45&$O$12:$O$45&$R$12:$R$45,0)), #VALUE! is the answer.
If I enter it as an array, the answer is #N/A. I tried making the 3 columns into a table (which I named Price1) and tried the formula again
=INDEX($D$12:$D$45,MATCH(MAX(Price1),Price1,0)),
But that did not work either.
I tried using a V-Lookup, but can only get results for the first column, not the other two. I've attached my spreadsheet.
Any formula that can look at the data in multiple columns and check for a match - returning a value such as YES or NO. Below is an example of my sheet.
A
B
C
D
E
F
G
1
Name
Number
Name
Number
Name
Number
Match?
[Code] ..........
Basically the formula would be in Column G and would look at B, D, & F to see if they match or not. In the example above - row two - they all match. So a True, 1, or Text option such as YES would be great. Same as row 3 only you can see they are different.
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 RelatedA code to transpose from rows to columns.
But why doesn't it transpose like the sheet 'How it should be' but all in one row?
HTML Code:Â
Sub MG10Jun34()
Dim Rng As Range
Dim Dn As Range
Dim c As Long
Application.ScreenUpdating = False
[Code] .......
I have 19 rows of data spanning 184 columns, and I'm attempting to transpose them to be 19 columns and 184 rows, which would normally be easy. The problem is I need to be able to transpose the numbers while still keeping the formulas on a separate worksheet linking/connecting to the correct cells. I almost need to be able to cut/special paste - transpose, but that's not possible... I don't think.
View 3 Replies View RelatedI have some sales history data for products which I need in one row. Each product has 4 rows x 12 columns (months) of data under each other. I need the 48 months of data in one row per product. A sample of the type of data I have is below (product A) along with the expected result. In the real data there would be many products not just one as per my example.
Sheet1 *ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAVAW1
Sample Data
[Code] .......
how transpose my 4 Colunas to 1 Row
Before:
Data1
Data2
Data3
Data4
[Code]...
how do i transpose from rows to columns
i have 545 names with addresses
Sheet1 GH221name23 adress line124 adress line225 phone26 fax272name28 adress line129 adress line230 phone31 fax323name33 adress line134 adress line235 phone36 fax37 city384name39 fax40 city Excel tables to the web >> Excel Jeanie HTML 4
selecting each block of name+address is tedious --how can i do it in one go
also the number of rows varies in each block -- at places there are 4 rows at other places 3 rows and so on
I have some summary results stated in row format by GL at the row header. I would like the GL to be the column header and the unit to be the row header. I tried using the index function but keep receiving #VALUE! error. Sample attached.
View 3 Replies View Relatedrefer to the attached file. I have series of continuous data which is numbered from 1 to 100. They are arranged in vertical order. Due to some application, i need to convert them into another sheet so that they can be viewed horizontally. I used HLOOKUP to obtain the data from sheet 1. Now i have only 17 groups of data and i still have 83 more data to be input in the future. Is there any VBA code that can help me "extend" the equation in sheet2 so that whenever i key in the data in sheet1, the corresponding data will be generated automatically in sheet2?
View 3 Replies View Relatedtransposing columns and rows in an Excel worksheet. This data is a table of part numbers requested vs. received over several different days. Currently, the data is sorted by date across the columns, and each date is split into two columns for requested and received. The rows are labeled with the individual part numbers.
I would like to format the data so that the part numbers appear as the column label, and each part number label is split into two columns for requested and received. Also, the date would then signify the date of each value in the table. I have attempted to use the transpose command, but this only gets me half-way there - still struggling with the splitting of the columns. I can drag a simple "= CELL" command, but the existing data is too cumbersome to build this. I thought a macro might be more efficient.
The file attached is a snap-shot of the data, but will display the formatting challenge I have. The worksheet "Given Format" is the current, while the "Desired Format" is the desired.
I want to transpose the phone number and website over where the name is? I know I can do a copy and paste special and transpose but then I have do it individuallly and I have over thousands rows.I have attached an example.
View 3 Replies View RelatedI need a macro that will combine/consolidate rows when cells from 2 separate columns match. example...
beginning:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 1064 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 80 unleaded 1101 100100
row 4 Seminole 30 dyed dsl 3421 100100
This is what I need the macro to do:
Column A column B column C column D column E
row 1 Seminole 80 unleaded 2165 100100
row 2 Seminole 36 clear dsl 825 100100
row 3 Seminole 30 dyed dsl 3421 100100
I have multiple rows of data which I have sorted by two identifiers. Where a set of data matches on both of these identifiers I would like to further compare the two rows based on a set of 8 criteria (in coulmns C-J). Where the rows match across both identifiers and across the 8 sets of criteria I would like to merge these into 1 row and copy to another sheet, where there is a mismatch on 1 of the 7 sets of criteria or if a row's 2 identifiers do not match another row- I would like the data to remain as is (or move to another sheet for non matches).
Please see below example.
For Pair 1 & 2: these match across the 2 identifiers and across all 7 sets of criteria so these would be shaded white and moved to a different sheet (or the addition of match at the end would also suffice).
For pair 3: these match on IDs but not on all 7 criteria again "no match" would suffice here.
For the final row it does not match on ID therefor I would need to "no match".
