Matching The Figures

Feb 10, 2010

I need to produce three different formats of accounts data monthly. I attached a file with each accounts description in Columns A, E and G. Is there a way to match the data with those accounts name in a faster way than manual linking?

View 13 Replies


ADVERTISEMENT

Compiling Partially Matching Data Into Matching Rows With Macro

Jan 20, 2013

My task is to combine two large databases into one spreadsheet by extending the number of columns. The data from each database is 90% matching based on an identification number, however occasionally there are additional rows or unmatched identification numbers that need to be kept for analysis.

When this happens, there needs to be a blank row inserted to represent the missing data in the rest of the corresponding row.

I am having trouble finding a quick way to do this because I have approximately 12,000 rows (and columns up to DV when combined).

for example:

p1
data
data
data
data
p2
data
data
data
data

[Code] .....

needs to become:

p1
data
data
data
data

[Code] .....

I am guessing I will need a macro of sorts, So far I have made one column that tell me if the ID's are matching or not (1 or 0) and if they are not matching (0) I manually insert the rest of the row that is missing or make space for the duplicate data (which needs to remain).

View 5 Replies View Related

Bank Reconciliation - Many To One Matching And Partial Cell Matching

Nov 19, 2009

programming 2 macros in a bank reconciliation sheet I am trying to create.

Basically the data consists of two sets: the ledger side and bank side. Both sides consists of multiple columns that include date, description and amount.

The following two situations can occur and which need to be covered with a macro.

Many to one matching
When we make payments to say 100 different suppliers at once, the ledger side will show 100 lines with the different amounts. However on the bank side it will only show one line with a total amount for the transaction. As the description on the ledger side is the same for all transactions done at the same time, it should be possible to have a macro add these lines and compare the total with entries on the bank side. Once a matching amount has been found, the macro should place an ‘x’ next to all entries on the ledger and bank side, in order to show that these transactions have been reconciled.

Partial cell matching
In this case we are only dealing with one line on both the ledger and bank side. The issue is that the description does not always perfectly match with one another. The ledger side might say ‘Brown Corp.’ and the bank side might say ‘Brown Corporation’. I want to come up with a macro that can recognize this partial match and still reconcile the lines by placing a ‘x’ to both the ledger and bank entry. The match however should be done with a combination of description, date and amount as several different bookings might be done for the same supplier in the same month.

View 12 Replies View Related

How To Show If The Figures Have Gone Up Or Down

Oct 9, 2008

I need to indicate if the figures for this week has gone up or down compared to last weeks figures.

So looking at the attached, I need to add a column (I5) to sheet Admitted Patients WE 20081005, so it compares the % treated in <18 wks figure by specialty to previous weeks figures on sheet Admitted Patients WE 20080921.

Ideally I would love an arrow either point up if the figures has increased or down if they have reduced.

View 8 Replies View Related

Cut And Paste Last 6 Figures?

Jul 1, 2013

I would like the last 6 figure in cell a1 to be highlighted cut and pasted into B1 - the amount to be cut will vary

View 4 Replies View Related

Duplicating The Last Row Of Figures?

Jul 15, 2009

I am trying to make an inventory cover page - that shows data of separate products on different sheets with the following;

Product
Date
Previous Total
Amount Taken
New Total
and occasionally there is a Re-issue of stock to the previous total.

This is at the moment an historical record as anyone can see the usage across the dates for any particular product by looking at the sheet. Each time there is a change to the inventory it is recorded on a new row. But what I am trying to do is to copy the last row of data from each sheet to the cover page to show an overall inventory.

The part where I get stuck is making the selection of the last entered row of data [from each sheet] automatically updating the coverpage, when each sheet will get new data added frequently.

I thought about somehow making a duplicate of the last row and locking it in place and linking that to the coverpage - but I still do not have a clue on how to get it to automatically select the last row of the data.

View 7 Replies View Related

Dim Figures From Cell

Nov 26, 2012

Dim figure from the cell b4,b5,c4,c5 to accumulate add total b11,b12,c11,c12, respectively.

View 5 Replies View Related

How To Add Space Between The Figures

Jul 28, 2013

How to make space between the figures ex:11480831525 i want like: 11480 831525

because i want to do it for thousand of transactions in one time by using only one formula.

View 1 Replies View Related

Alignment Of Figures

Sep 17, 2007

I have the following ...

.Offset(3, 0).Value = "For " & P & " numbers there are " & Format(tly, "###,###,##0") & " x different values of " & cmb & " numbers. "
... which includes figures upto and including millions.

The thing is that the above code ONLY produces figures that are relevent.

View 9 Replies View Related

Matching Two Tables - Copying Data For Matching Value?

Feb 27, 2014

I have two tables, Table1 one has only customer codes in it, and I have Table2 with plenty of customer codes and those customer name, surname, age, location...

And I want to match and copy each of those customer codes in Table 1 all the information which is on Table2

Table 1:
50025
50026
50086

Table 2: Considering that name, age and location is each in separate cell

50025JohnSecond25Location 1
60085EmilyThird 20Location 2
45454Wilhelm Fourth35Location 3
10000RoseFifth 60Location 4
50086JohnySixth 45Location 5
65501JacobSeventh18Location 6
50026Jackie Eighth22Location 7

And the outcame should be that it finds the value from Table1 in Table2 and copy's information in Table 1 like this:

50025 JohnSecond25Location 1
50026 Jackie Eighth22Location 7
50086 Johny Sixth 45Location 5

View 2 Replies View Related

Copy Matching And Non Matching Data Into New Worksheet

Feb 20, 2014

I have two worksheet "ABX" and "ACX" from which I want to find the matching data for specific columns B, D and E only.

Matching data will be in sheet "Match" and vice versa.

Data are present dynamically.

View 1 Replies View Related

SUM Formula For Certain Figures In A Column?

Jul 28, 2014

I want to calculate the sum of a column but I have 4 lots of figures going down the column and I only want it to calculate the some of 2 of those 4.

The 4 figures: £4.80, £3.80, 31 & 25.

These 4 figures are within the column multiple times and I just want to have one cell that sums up the £3.80's and £4.80's within the column and then another cell to do a separate sum of the 25's and 31's.

Is this possible, if so how? Does it require an IF SUM formula of some sort?

View 5 Replies View Related

Rounding To Significant Figures?

Mar 31, 2014

I have a large number that has 6 figures.I want to display this number to 3 significant figures on the spreadsheet, but I have more formulas that need to use the unrounded number.So is there a way to DISPLAY the number to 3 sig figs whilst keeping it as the complicated number for the rest of the formulas?

View 6 Replies View Related

Summing Figures Which Include #N/A

Feb 3, 2010

how can I total three columns together, one with the value of #N/A, and two with figures in, without the total coming back as #N/A ?

View 10 Replies View Related

RANKING Set Of Figures Using Criteria

Jul 11, 2012

I'm wanting to Rank a set of figures in one column but use criteria in another column to pick up the array which is used. I don't want any figures to be ignored so will have multiple rank 1's 2's etc. Would also be handy if I could use multiple criteria.

View 9 Replies View Related

HLookup For Words Not Figures

Jul 15, 2012

I am using a Hlookup to retrieve Words and Figures but when retrieve words it is failed and output is figures ONLY while I need the same word to be shown in the cell value?

View 2 Replies View Related

How To Get Final Figures From Raw Data

Feb 13, 2014

DATE
PRODUCT
BALANCE

1/2
KEY CHAIN
4

1/2
KEY CHAIN
8

1/2
KEY CHAIN
27

[code]....

How to get final figure each product from sample data above:

the result:

Key chain = 27
Gift = 4
Hamper = 1

View 7 Replies View Related

How To Paste Figures From Column To Row

May 7, 2007

I have 2 spreadsheets, one is on crew costs and the other one is a summary page. On "crew cost" worksheet, say A1= IBM, B1 =Microsoft, C1=HP and D1=Pioneer. I want to link A1 to D1 to cells A1 to D4 on "summary page".

However, I do not want to use "paste special", "transpose" function as I may need to add in columns to "crew cost" spreadsheet.

Instead I hope that I could link A1 on "crew cost" spreadsheet to A1 on "summary page" spreadsheet. Then I hope to drag the formula from A1 down to D4. The result will be A1= IBM, B2 =Microsoft, C3=HP and D4=Pioneer on "summary page" spreadsheet.

However, it does not work as dragging only occurs through columns or through rows. Is there any formula that I can use?

View 9 Replies View Related

Formula Account For All Possible + Or - Figures

Nov 23, 2007

I am working with percentages, and trying to avoid #DIV/0! Errors at the same time.
Lets assume column A holds last years figures, and column B holds this years figures, column C calculates the difference in percentages.

I am having dificulty where say A1 = 0 B1 = 10 (C1 Should be 100%)
then what happens if A2 = 5 and B2 = 0 (C2 Should be - 100%)

Is there a common formula that will account for all possible + or - figures in A and/or B that will also suppress #DIV/0! Errors?

View 9 Replies View Related

Finding Figures That Add To A Total

Apr 7, 2009

I have a list of 30 numbers representing receipts due. I have one receipt which I know must represent the sum of some of these numbers. Is there anyway in Excel that I can highlight the range of 30 numbers and ask Excel to sample all possible combinations to find which numbers add up to my total receipt?

View 9 Replies View Related

Displaying Significant Figures

May 9, 2009

I have an excel database with about 5 columns and 150 rows whose cell data I want to display to 2 significant figures. Almost all the data is numeric, but there are one or two cells with text in them, and the numeric data varies between 1 and 4 significant figures at the moment, some >1, some

View 9 Replies View Related

Deleting Rows With Zero Figures

May 31, 2006

Im trying to find a way to script a button to delete entire rows from a selection that contains rows which total 0. It seems like an easy task but ive drawn a blank.

View 5 Replies View Related

Round To X Significant Figures

Aug 31, 2006

I'm trying to find a way of displaying results from a worksheet which can range from a few thousand to several million. However I only want to report to a set number of significant figures(5), i.e. if the result is say £25,256,128 I would like to show the resuilt to the first 5 significant figures i.e. £25,256,000, and say £3,508,569 as £3,508,600.

View 4 Replies View Related

Round To N Significant Figures

Feb 26, 2008

I have been trying to find a way to take the result of a formula, calculate how many decimal places based on significant figures, then rewrite the formula with the Round function.

=(5-2)/2

result is 1.5 taken to 3 significant figures is 1.50

=Round((5-2)/2),2)

I need to keep the formula in the cell for validation purposes and I need the result truncated to significant figures for accuracy. I am currently using the significant figure formula found on the forum for my data with numbers.

View 3 Replies View Related

How To Overlay Different Totals Figures With Filters

Mar 13, 2014

I am doing a budget sheet in Excel for the different expenses accrued by the different classes we offer in our university department (honoraria for guest lectures, tickets to shows or museums, etc.). The sheet is formatted as a table and I have the Total Row enabled at the bottom.

However, we have a reserved budget for different things. For instance, Fall 2013 museum tickets has, say, a budget of $1,000. Fall 2013 honoraria might have a budget of $2,000. I want to be able to filter to see only Fall 2013 honoraria: the totals row will reflect this, but I also want it to automatically tell me how much money is left in that budget (i.e. I want it to know to subtract $2,000 from the totals figure because that is the budget for Fall 2013 honoraria).

It gets a little more complicated because we also have a budget for each class too. One class might have both honoraria for guest lectures and field trips to a museum, and we might say "We don't want this class to spend more than $500." Again, I want to filter to see just this class, and have the Totals row also show me how much money is remaining for this class.

View 4 Replies View Related

Grabbing Data (figures) From Different Worksheet?

May 23, 2014

i am currently constructing a table on excel and need it to grab a figure which is constantly changing from another worksheet.

View 2 Replies View Related

Creating A Formula To Add 30% To Worksheet Figures

Oct 10, 2008

I have a late report due @ work.... I have people who have worked for 4 days to get me numbers i need and they are not right.....

Anyways I have a spreadsheet with a whole lot of numbers on it... I need to add 30% to each number. What is the easiest way to set this up.

View 13 Replies View Related

How To Calculate Year To Date Figures

May 12, 2009

I'm trying to come up with a formula that calculates a year to date target, taking into consideration the actual working days and the target for those days.

So for instance today is the 12th of May and my year to date percentage is 40% but it should be 100%. How do i create a formula to calculate that?

View 9 Replies View Related

Blank Display If Figures Not Yet Entered

Jun 19, 2009

I have this formula in a cell:

=IF(AND($D4=H4,$F4=I4),3,IF(SIGN($D4-$F4)=SIGN(H4-I4),1,0))

but i need to somehow incorporate a formula that only shows this if a cell has a value present.

I know as an example its something like:

=IF(C2="","",C3)

but i dont know how that would go in my existing formula as i dont know how to stack IF statements?? (a very kind person on this forum did the original formula btw)

the cell that i need to have as the 'blank or not' is D4.

View 3 Replies View Related

Round To Significant Figures Using Excel?

Dec 20, 2013

How to round to significant figures using excel, its for a question in one of my college assignments and i cant seem to find any information.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved