Transpose From One Worksheet To Another Based On Several Criteria

Feb 10, 2013

I have several worksheets in one workbook entitled, "A", "B", "C", "D", and "E". In A1 of each worksheet is the title of the respective worksheet. In A3 is the word "Day", while in rows A4:A34 are the numbers 1 through 31 (corresponding to days of the month), in the date format "1/1/2012", etc. In row 3, columns B:F contain dates of the first day of a respective, consecutive month and year spanning ther period 1/1/2012 thhrough 3/31/2013. For example in B3, the date 1/1/2012 appears, while in C3, 2/1/2012 appears, etc. all the way to P3 the date 3/1/2013 apppears. In the "body" of the table (B4:F34) is daily data corresponding to the respective day, month and year.

What I would like to do is "transpose" this data to a "Master" worksheet set up as follows: in row B1:F1 would contain the names of the individual worksheets (A, B, C, D, and E). Range A2:A457 would contain daily dates beginning with 1/1/2012 and ending with 3/31/2013. In the "body" of this worksheet, i. e., B2:F457 would be the daily data corresponding to the respective day, month, year and worksheet.

Transpose Multiple Columns To Rows Based On Criteria

Sep 26, 2008

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.

Auto Copy Part Of Row In A Worksheet To Another Worksheet Based On Criteria

Dec 15, 2009

I'm a novice Excel 2007 user and appreciate all the help I can get. I have a workbook with monthly worksheets in it. When a certain data Type is selected from a drop down menu in that monthly worksheet than I would like to have it automatically enter specific data (Name, Date, Eval, Type) copied to another worksheet (CC) in the same workbook. I have been manually entering the data so far. Another thing, some of the data will be entered into the Monthly worksheets and some will only be manually entered into the CC worksheet so it would need to accomodate both methods of data entry. Please let me know if I need to clarify. I have attached the workbood, too.

Updating Contents Of One Worksheet Based On Criteria In Another Worksheet (2)

Mar 10, 2009

OK, two files attached in the zipfile, pricelist-half.xls and pricelist-full.xls

The background is that i exported a file from our warehouse system so we could update prices and re-import it. That is the pricelist-half.xls

After our staff had spent a few days working on it, it came to light that (as the name suggests!) its only about half the products that should have been exported that are on the list.

Hence the second file, pricelist-full.xls which as its name suggests is the full
product list.

What I need to happen, to make this as painless as possible, is somehow for the items/rows that are on the full list, but not on the half list to be inserted to the half list but also have a yellow background for their rows so they stand out clearly.

Populating One Worksheet With Data From Other Worksheet Based On Criteria?

May 22, 2013

I have two sheets in one workbook; one has a list of names in column A (136 of them) and another is a master list with all the names of people who work for the company (over 2000). Sheet 1 and Sheet 2 respectively. I need to populate columns B-E in Sheet 1 with the data in columns B-E in Sheet 2, but only for the names that match in column A. I've tried applying filters but can't get to the data I need for some reason. The names are formatted the same way in both sheets (Last, First). In Sheet 2 the names are links but in Sheet 1 they aren't; not sure if that's important.

Populate Worksheet From Another Based On Criteria

Dec 18, 2006

I have a master list of people. (call it master) I have 1 other worksheet that should contain everyone aged 15 and up (call it must progress). One of the columns on the master show's their ages. I was hoping excel would automatically populate the sheet based on the age from the master list. So, for example. On the Master list, Joe Smith is 17. I would like Each cell in the Joe Smith Row to automatically copy itself to the must progress worksheet because his age is over 15. The formula would be based off the age on the master.

Cut Data Based Upon Criteria And Move It To New Worksheet

Jan 10, 2012

Error in the code, where I'm trying to copy the data (based on criteria) from one worksheet to another, then delete the data from the first worksheet. In the code (I have complied from this message board) I get an 1004 error "Application-defined or Object-defined error".

Code:
Option Explicit
Sub CopyALColKYes()
Dim NR As Long, c As Range, firstaddress As String
Application.ScreenUpdating = False
NR = Sheets("Completed-Expired").Cells(Rows.Count, 13).End(xlUp).Row + 1

[Code] .........

Copy Row Data To Another Worksheet Based On Criteria

Sep 28, 2006

Data exists in a workbook's sheet name "0293" in columns A, B, C, & D. If a row's value in column B exceeds zero, the entire row & formatting needs to be added to the bottom of data in a sheet called "Comprehensive" of the same workbook and put 0293 in column E of the same row. What would be the simplest VBA code to copy sheet 0293's data & add it to the "Comprehensive" sheet with the sheet name in column E of the same row?

Link Row Of Data From One Worksheet To Another Based On Column Criteria

Oct 11, 2012

I have excel sheet with 4 worksheets tabs(Master, Won, Lost, No Bid). All data entered on Master. I want each row to automatically link/appear on 2nd, 3rd or 4th worksheet tab based on info in column Q (Result) which would be either 'Pending' - in which case it would stay on Master, 'Won' - would stay and copy to Won worksheet, 'Lost' - would stay andcopy to Lost worksheet, 'No Bid' - would stay and copy to No Bid worksheet. All rows would always show up on Master worksheet.

Moving Rows To Anoter Worksheet Based On Certain Criteria..

Feb 11, 2009

I have an excel workbook with 5 sheets, the first called 'data drop sheet' & the others called 'Louise', 'Thor', 'Peter' & 'Steve'. In the data drop sheet, the first row is header data and underneath (A2 onwards) are rows of data that I have cut & pasted in. The first column (A) of this data will be the person's name, e.g. Louise Wilson.

I want to be able to move the rows for each person to their respective sheets, starting from row 2 as the first is a header. So, for example, if 'data drop sheet' cell A2 says Steve Brady, I want it to copy the whole line to sheet 'Steve' row 2 etc.

Deleting Workbook Based On Criteria In Active Worksheet?

Aug 16, 2012

I am trying to delete a workbook, yes the whole workbook, if cell A1 is blank.

VBA Code To Copy Rows To A New Worksheet Based On Criteria

Dec 17, 2008

I need a macro that will search data from a range of cells in one column for multiple criterias and them copy the entire rows to a new worksheet.

Example I have a list of group names

Network
Telcom
Help Desk
BA
Network

I only want to choose all Network and Telcom rows copy to another worksheet.

Copy Rows To Another Worksheet Based Upon Multiple Criteria

Apr 8, 2009

I have seen all kinds of copying routines, but haven't been able to get what I want.

1. Copy Row contents from activeworksheet

2. The activeworksheet will have various row counts

3. The activeworksheet has row 1 as header.

4. The criteria to use is in the current worksheet columns P through Z

5. The criteria to look for is the word inspect. only the word inspect, not words that contain inspect (i.e. "inspection") should not be found.

6. The word could be in column P and in S, but needs to be only copied once, becuase it has meet the criteria, but it also could only be in c

7. Copying the row into a Sheet named "Inspection"

As a bonus would be cool to be able to copy certain cells or the entire row.

Copy Multiple Columns Based On Search Criteria To Another Worksheet

Sep 27, 2011

Rep Name is Column J Row 5. I need to find multiple columns based by the header which is row 5, if column range (j5:az5) is "Video - Actual" then copy column to sheet "Data", this needs to be repeated for about 8 more times for different criteria

Rep NameBundle - TargetBundle - ActualVideo - TargetVideo - ActualJoe, Jane
2.06.06.04.0Smith, John
7.010.02.06.0Tucker, Chris
15.011.06.010.0Iglesias, Gabriel
8.03.06.010.5Smith, Will
8.03.06.06.5Powers, Austin
10.025.07.09.0

Query Worksheet & Return Rows Based On Conditions/Criteria

Jun 1, 2008

I am trying to query a names sheet where each row may contain more than one occurence of a member ID. There are no duplicate rows ( records), because the dates are different for all rows.

1. I want to retrieve all of the rows from the names sheet that match a unique member ID. So far I only get one row.

2. And if mistype the number, it gives me a wrong record .I would like to get an error message that if I don't get a match, that it appears in the somewhere like dialog box or even entry in one of the cells that no name exits.

VBA Lookup Function: Paste Data Into A Cell In Another Worksheet, Based On Criteria Specified In Sheet1

Jan 8, 2009

I'm looking to create a macro that will take data from an input sheet, and paste it into a cell in another worksheet, based on criteria specified in sheet1.

Specifically in the attached example, the macro would copy the data in cells C8:C10 of sheet 1, then paste them into sheet 2 based on the data specified in cell B3 i.e. it would paste them into the column headed Mar-09. I intend to make this cell a drop down, so that the user can then select the next reporting month and run the macro again to paste the data into the Apr-09 column.

Copy Column Data From Multiple Worksheets To Single Worksheet Based On Header Criteria

Oct 18, 2013

I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.

I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.

Transpose Function: Convert The Worksheet

Nov 18, 2008

I currently have a Tab named "Rent by Month 3-08-5-29" containing the monthly rent amount for 700 locations for March 2008 - May of 2029 with the layout seen below.

A B C D E
Shop# 3/1/2008 4/1/2008 5/1/2008 6/1/2008 ETC..
2 \$5000 \$5000 \$5000 \$5000
3 \$3000 \$3000 \$3000 \$3000
4 \$7000 \$7000 \$7000 \$7000
18 \$4000 \$4000 \$4000 \$4000
ETC... continuing down 700 rows and over 251 columns

What I need to do is convert this worksheet so it contains the same information but resembles the following layout.
A B C
Shop# Month Rent Amount
2 3/1/2008 \$5000
2 4/1/2008 \$5000
2 5/1/2008 \$5000
2 6/1/2008 \$5000
3 3/1/2008 \$3000
3 4/1/2008 \$3000
3 5/1/2008 \$3000
3 6/1/2008 \$3000
Etc....

Copy Range Groups & Transpose To Another Worksheet

Oct 22, 2007

I have a workbook with data in it that I want to copy and put in another wookbook. The data looks like this.

D11/22'2005
CX
T-7.80
PEl Azabache
LDining Out
(blank cell)
D11/22'2005
CX
T-9.50
PAce Hardware
LHousehold
(blank cell)

What I want is a macro to copy that data until it finds a blank cell and paste/transpose that data in another workbook and then get the next set of data until an other blank cell.

It should look like this when done.

D11/22'2005CXT-7.80PEl AzabacheLDining Out
D11/22'2005CXT-9.50PAce HardwareLHousehold

Transposing Macro: Take A Set Number Of Consecutive Entries And Transpose Them To Another Worksheet

Mar 3, 2007

I work on some High-throughput Projects analyzing biological samples. The software we use for analysis spits out the data into excel. This is all well and good, being mostly automated. However, my issue arises when I get the output data. It's simply one long list of values in a single column. This could potentially be 384 entries. Depending on the parameters of our experiment, I'd like to be able to take a set number of consecutive entries and transpose them to another worksheet. However I don't know how to loop the function to get the specific entries transposed to their specific worksheet. I'm pasting a recorded macro I made to illustrate my point.

Sub HTSoutput1()
Range("B2:B9").Select
Selection.Copy
Sheets("K562").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "2330"
Sheets("Nuclei").Select
Range("B10:B17").Select.............

Transpose 1 Column Based On Another

Nov 3, 2009

I have a spreadsheet with the following columns

No Of Transactions
Total Revenue
LID
OURSUB
SubCount
Name
Phone
Phone2
Email
Phone3
Phone4
Phone5
Phone6
Phone7
Broker
Bcode

There are 28,000 records in the spreadsheet.

The column LID contains the ID of the client. Each client can have transactions in multiple subs ("oursub" column). The spreadsheet is sorted by the LID column.

What I need is for the "oursubs" values to be transposed column headings for each unique LID. E.g. if "LID" 12345 has 50 records with 25 different "oursubs". I want one row with LID "12345" and 25 columns with the names of the 25 "oursubs".

I am attaching two sample spreadsheets. The spreadsheet "LID_Source.xls" contains sample source data and the spreadsheet "LID_result" shows the result that I want.

Transpose Columns To Rows Based On Titles

Jul 9, 2007

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..

Transpose / Repeat Values In Column Based On Count?

Aug 27, 2010

I was wondering if there was an array formula or some VBA code which can transpose/repeat values based on a count. Below is an example of what I mean:

Name Count Output Tod 4 Tod Bill 3 Tod Sue 5 Tod

Tod

Bill
Bill
Bill

Sue
Sue
Sue
Sue
Sue

Transpose Multiple Columns Into Rows Based On Repeats

Jul 9, 2009

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".

Transpose Multiple Columns To Rows Based On Match Via Macro Or Formula

Jul 10, 2008

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.......

Sum Range Based On 1 Criteria Of Column & 2 Criteria Of Another

Mar 4, 2008

i m trying to use the sumproduct formula, and OR but i cannot seem to get this right! =Sumproduct(--(A1:A10="Yes"),--(OR(B1:B10="Yes",B1:B10="Mayby")),C1:C10)

I have also tried Array Formula as follows; {=SUM(IF(A1:A10="Yes",IF(OR(B1:B10="Yes",B1:B10="Mayby"),C1:C10)))}

I have also used UDF to for the sumproduct, but cannot make that work! keep giving me value message

Function
Function Customer(Service as Range, Outcome as String, Service2 as Range, Outcome2 as String)

Customer = Sumproduct(--(Service = Outcome),--(Service2 = Outcome2), Result)

-Didnt get thru this bit to start building on the Function! keep giving me #Value!

Transpose Multi-Column Groups/Repeats Based On 1 Column

Mar 19, 2009

I have a sheet with 3,000 rows and only two columns. Column "A" consists of 20 to 30 different names, column "B" consists of 50 to 60 different products. I need to be able to evalute the value in column A and copy, paste special transpose all values in Column B that have the same value in column A. As an example if cells A1 through A5 is "Arizona" and cell B1 is Broccoli, B2 is Cauliflower, B3 is Apples, B4 is Oranges, and B5 is Bananas, I want to copy B1 through B5 and paste special transpose to cell C1.

This then would need to loop all the way to the bottom of the data in Column A looking for a change in value. The attached file called Sample Data has two tabs. The one titled "report" shows the raw data, the one titled "Final" shows how I would like the results to appear (column L)

Auto-Populating Multiple Values Based On Two Criteria Based In Comboboxes

Aug 7, 2013

I'm starting a dashboard, where on the front page I have two combo boxes on the left, and three empty fields to the right. I'd like the three fields to the right to auto-populate table-based values depending on the chosen criteria from BOTH fields (by store and month/date). I've attached a sample of what I've got so far. I've only provided three tables for this example, and I have a table with the same column/row titles for each metric and I have three different metrics I'd like to auto populate: COGs, Sales, and GM% or in the example, metric 1, metric 2, metric 3. No pattern in the table values, just wanted to populate the fields quickly. All fields are organized by store/month-date and I've set up a link to my combo boxes on a calculations tab.

Transferring Columns To Another Worksheet Based On Entry In Main Worksheet?

Oct 17, 2012

I have an order form created in excel with a list of about 1600 products. I have a column set up for the customer to place the ordered amount of each product. What I need now is a way to transfer only the rows that have a value in the "ordered" column to a new sheet. I have seen it before , but don't know how it was accomplished.

Formatting Worksheet: Re-adjust The Worksheet To Make It More User Friendly Based On Number Of Rows In Current Sheet

Oct 14, 2008

I have a worksheet with 30,000 rows. But sometimes even if I have fewer records in this worksheet(lets say 1000) worksheet shows the same 30,000 rows.And its annoying when you try to navigate using vertical scroll bar. Is there any option to re-adjust the worksheet to make it more user friendly based on number of rows in current sheet.