Match Columns Of One Table To Another Two Columns Of Another Table

Jun 27, 2007

I need to Match to columns of one table to another two columns of another table, both tables are on the same worksheet.

One table has 500 rows more that should not be, I need to find out which of these 500 rows should be deleted, so the two tables should be same. So the batch number and corresponding amount should tie to another tables batch number and amount.

View 9 Replies


Match Two Columns To Data Table

May 28, 2009

I have a volumes sheet and based on two criteria (in columns) am trying to pull the corresponding value from the April Results Table. I have product type in C2 (this varies down the column) and depot numbers in D2. The table i am trying to match has depot numbers in column A2:A93, and Product type in Row 2 (B2:P2), where the two criteria are matched, i need it to return the corresponding figure. I have tried various combinations of Index and Vlookups but i cant get it to work!

View 5 Replies View Related

Convert The Data Shown In Table 1 To Table 2 Without Rearranging The Columns And Rows

Sep 11, 2009

Is there a function to convert the data shown in table 1 to table 2 without rearranging the columns and rows? because i don't want to use TRANSPOSE. I want a function, somthing like SUMIF with OFFSET or INDEX and MATCH or any other function.

Table 1

Team 1Team 2Team 3Team 4Team 4Team 5Team 5ABABCity 12531642City 231173705City 367891125City 436251348

Table 2

City 4City 2City 1City 3Team 4BTeam 2Team 5ATeam 4ATeam 1Team 3Team 5B

View 2 Replies View Related

Fill Table By Match Row & Column Headings Of Another Table

Aug 29, 2007

I currently have a table with a range of headings (row & column), and the necessary data for it. On a new worksheet, I have a table with only a few of the headings, and I was wondering if there was a macro that would automaticlly match the headings of the new sheet with the other table, and fill in the ncessary dat, as on the other sheet.

View 3 Replies View Related

Sum Multiple Columns In A Table

Jun 12, 2014

I would like to sum multiple columns in a table driven by certain criteria. The criteria is text and it is specified by a text function that is date driven and it also includes a wildcard. Essentially, this table contains YTD information, each month a new columns are added. I would like the formula to update automatically when the new accounting period date is populated.

I've tried sumif( and sum(if( using an array, but was not successful.

View 5 Replies View Related

2 Columns Table Without Using SORT

Mar 1, 2009

I have some table with 3 columns: Name, Team, Players with the same name

I want to get to a 2 columns table without using "SORT" or something like that... using only formulas.

Maybe I wasn't so clear so I added a file with my problem.

View 11 Replies View Related

Convert Table Into Three Columns

Aug 27, 2009

I am trying to convert a table into three columns so that I can use the data in a vlookup.

View 8 Replies View Related

Add Columns To Pivot Table With A Name Like ABC

Jul 14, 2014

I have a pivot table that I create weekly to include the new week's data, as well as the rest of the weeks in the year. I'd like for the macro to pull all of the columns like "WE" into the pivot table so that it includes the current week. Below is what the code looks like now. Is there a way to tell the macro to pull columns based on "field name like" language? I've been able to get this far, but it doesn't loop the headers to find all of the columns that have WE headers.

Sub AddPTFields()

Dim PT As PivotTable
Dim PF As PivotField
Dim FldName As String
Dim LastCol As Integer
Dim TxtStr As String
Dim wsName As String


View 2 Replies View Related

Pivot Table - Row To Columns

Mar 15, 2013

I have a pivot table that shows the following:

2 XXAL______213_________1
3 XXAA______213________1

...and the list goes on for 1300 more ID items

What I need is to reformat it to show the location2 as columns one next to each other as follows:

1 XXAT 213 215 226
2 XXAL 213 228
3 XXA 213 123 258 065...

View 1 Replies View Related

Hiding Columns With Zero Value In Table

Nov 23, 2006

I was wondering if there was a simple function that will hide all the columns which have Zero value thoughout a pricing spreadsheet.

The different elements of the pricing have lead to 60+ columns, upto 75% may not contain a value or may display "false", it is making it very difficult to view and print. At present I am manually auto filterig to see if values are present in the column and then hiding the columns if they are blank (a real pain in the backside).

View 9 Replies View Related

Format Table & Add Sum To End Of 2 Columns

Mar 1, 2008

I have a code to apply formatting to my selected cells (a border and background color) as well as sum in rows in columns C and D. I have data in A1:D1508. The first row is titles. Column A has item codes, B is item titles, C is 2007 sales and D is 2006 sales. All the items are grouped by categories. For example A1:D7 is category 1 with row 8 containing a total in column C and D, A9:D14 is category 2 with totals in row 8. Not all the categories have the same number of rows. This code is giving me a Run-time error '1004': Application-defined or object-defined error. I have searched the forums and can't find what is wrong with my code.

Sub CustomFormating()
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With...............................

View 9 Replies View Related

Add Columns With Calculations In A Pivot Table

Nov 2, 2012

I have two queries regarding pivot table.

I would like to include additional columns with formula in to a pivot table

I would like to get ride of the Items in a pivot table with Zero values without editing the source data.

I have attached a work file in to the below link, [URL]....

View 1 Replies View Related

Can Add A Subtotal Of 3 Columns In A Pivot Table

Nov 22, 2013

I want to get a subtotal of columns B C and D in a pivot table. I have tried to add a calculated item to a pivot table to add columns B C and D. When I try adding a calcuted item I am getting an additional column inserted after columns B C and D. Each additional column has the previous column duplicated. I want a subtotal of column B C and D. I don't want to use the grand total function because I also have columns E through H that I don't want in the subtotal.

How can I get the columns B C and D subtotaled within the pivot table?

View 5 Replies View Related

Transpose Multiple Columns Into A Table?

Aug 18, 2014

I have an problem transposing multiple columns into a table. Source data is organized in 3 columns - ID, Visit#, Date. What I need is a seet in which I'd have in Column A - and ID, in Row 1 a Visit type and Visit date would be populated in the table. See attached file. Since I'm handling about 50k datapoints a simple "paste special--->transpose" is a nightmare.

View 5 Replies View Related

Format Pivot Table Columns?

Oct 24, 2011

How can you visually format a column with multiple values in a pivot table? Say you have month in the column section, and three values in the value section: budget, actuals, variance; I want to visually seperate each month's set of data.

View 1 Replies View Related

Move Pivot Table Columns?

Mar 26, 2013

is there anyway to arrange pivot table columns without manipulating the field list items?

in other words, in older versions of excel, you could just right click the column and select move left, or move right

View 2 Replies View Related

Add Concatenation Of Two Columns To Pivot Table?

Jul 18, 2013

I have a large data set that I want to pivot on to produce various results. I can easily pivot on two columns to produce those Unique IDs where East AND West are "Yes" or "No." I want an easy to filter on results where either East OR West is "yes." I could do this with a joined (Concatenated column) such as East/West but was hoping there might be an easier solution built into Excel pivot feature.

Unique ID






View 2 Replies View Related

Rearrange Columns In Particular Manner In A Table

May 9, 2014

I create a report where in I need to rearrange the columns in a particular manner as required by the client. Any macro which will will work as per following criteria.

Column headers start from A1 onwards (the last column header might vary depending on the creator, I get the report from a different team). The number of rows of data might vary.

For changing the order of columns, the macro should look for a column with the header "Close Date", cut it and insert is before a column with header "Bookings". Then, the macro should look for a column with the header "Description", cut it and insert is before a column with header "Selling BU". The macro needs to then cut two adjacent columns namely "Sector" and "Sub-Area" and insert it before the column with header "Total Bookings".

View 2 Replies View Related

Determine The Age Of A Value In Respect To A Table Of Columns

Jan 13, 2010

I'm trying to determine the age of a value in respect to a table of columns and just cannot seem to nail it - perhaps it's my logic .

1 year2 year3 year4 year5 year5 year +Balance395.0384769.77271115.9771436.9441738.5292009.48775

I have a balance (in this case, 775), I need to determine where it sits within the table.

For example, if my balance is less than a year, retun "1 year less". If it's greater than the one year, but less than 2, return "1 year old"; if it's greater than 2 years but less than 3, return "2 years old". I need to do this all the way until it's greater than 5 years.

I guess it's an IF statement, but my calculations keep churnin out 2 years, when in fact, we can clearly see it's 3 years (because it's greater than the 2 year value).

View 9 Replies View Related

Pivot Table Non-Adjacent Columns

Jan 7, 2007

Pivot Tables. The structure and subject-content of cells B3:D14 (Block A) is identical to those of cells F3:H14 (Block B). I want a pivot table to treat the two Blocks as if they occupied the same three columns (e.g. B3:D26).

View 3 Replies View Related

Pivot Table With Multiple Columns

Jun 28, 2007

How do I create a pivot table with multiple data columns? My fixed asset software will not let me create a report to list multiple months/ quarters. I've created a spreadsheet that I can dump each month into, but I'd like to be able to sort by G/L acct or Department. When I try to create a pivot table, I can't get it to accept each month as a data field. Ive attached a copy of the spreadsheet that I'm using & the report that I'd like it to look like.

View 4 Replies View Related

How To Transpose A (table) Column Into Multiple Columns

Jul 10, 2012

This is a sample of the attached workbook.

Current Phase
Start Date
Planned Finish


What I am trying to do is break up the "Phase" column into several columns, where each phase would have its own column. Something like this:



It is very important that the output of the data goes on a new sheet, and that the records maintain integrity.

View 3 Replies View Related

Pivot Table - Calculating Percentage Of Two Columns?

May 14, 2014

why it works (but it obviously does). I crabbed off the sheet, modified it with my data and the percentages calculate reliably.

What I can't figure out is that in the Pct calculation in the table is the formula:


The formula itself makes perfect sense in terms of the numbers and filtering involved....but why does it actually work and what does the "^2" do?

View 2 Replies View Related

Pivot Table - Group Columns By Date

Jun 16, 2014

Not sure if this is possible but I have a sheet of data which is effectively a project plan. It has a list of resources, role and phase etc. I then have lots of rows which are w/c dates and under each "week" I have the amount of hours or days they work. So for each person I can record their hours etc.

I then need a Pivot which basically collates the data and displays each phase's total hours but month. There maybe multiple phases or and each person may appear in each phase

I've attached an example - Excel Problem.xlsx‎

View 1 Replies View Related

Calculate The Difference Between 2 Columns In Pivot Table?

Aug 14, 2014

So I am running in to a large brick wall! I have a single pivot tablet with a column for items received and then a column for items shipped. I would like to create a calculated field in the pivot table that will subtract the items received from the items shipped columns. I have attempted to add the calculated field using the tool bar but it keeps giving me "0" for every item on the pivot table when there are obvious differences.

View 1 Replies View Related

Pivot Table With Columns Listing Counts

Dec 10, 2012

I have a spreadsheet similar to this:

CA ...........Cat................................

I want to create a pivot table to do this:

CA ...............1.............................

I have been unable to get it formatted correctly. The closest I get are "sub-colums" with counts.

View 2 Replies View Related

Adding Columns To External Data Table?

Sep 11, 2013

I have a External Data Table, but I wanted to add a extra column to it for comments. I just need to be able to write down what I did with that particular order or things like that. I did make a column, but the problem is whenever I update the table the comments column fields stay static I mean they don't move with their row. So it ends up the comment belonging to a row is now on another row.

is there a way I can tell excel to move the cell with the record or something like that.

View 2 Replies View Related

Formula To Expand Headings To Table Columns

Jan 25, 2014

The attached workbook has a fragment of a table . The column heads are letters representing job nos. The first row is of numbers which will always be multiples of 1000. Subsequent rows are the factors of each number by each number.

My problem is how to structure the columns of the second table so that if a number has a factor of 1 there is one copy of that column head. if the number has a factor of 2 then there are 2 copies of the head, and so on.

The table can vary in size but the first row of each column will always be an integral number of 1000's (e.g. never 1250).

I have not been able to attach the workbook as "ADDFILES" is not visible in the download screen, I hope the above description will be enough.

View 3 Replies View Related

Rearrange Table - Transpose Rows To Columns

Nov 2, 2007

I have problems to write a macro to rearrange the table from attachment file sheet1 to sheet2. I have many files that need to transpose rows to columns and with continually years, month, day, and the rest parameters. some to the files many content more then 20 years data.

View 5 Replies View Related

Looking Up Value In Table (multiple; Unique Id; Columns For No Vlookup)

Mar 30, 2009

What formula should I use so that it will find the match with a 6-column table (with every other column containing the 'reference' items, so I can't use vlookup)

So that it will return the value next to the match

(instead of returning gibberish, will return a number instead)


Okay had that table setup that way because I needed separate tables ("linked" comboboxes")

So I just altered it so that the vlookup range changes dependent on the value of combobox 1.

View 4 Replies View Related

Copyrights 2005-15, All rights reserved