Match Value In One Row To That In Another Row That Shares Common ID

Feb 11, 2014

I am looking at some combination of Index, Match and array functions in order to assign a value, in this case a currency, based upon an instance where another data item with the same trade ID already has that currency assigned to it.

Basically I have a list of data where there are a list of trade IDs for each data row/item.
In some cases there will be two rows of data that have a common trade ID.

For example there may be two results that share a trade ID eg 450222, one in EUR, one in another currency eg GBP.

What I need to do is create a formula in other column to the right of my data that recognises that if a trade ID is assigned to a non-Euro currency then to apply the foreign currency to both trades with the common ID.

ie change

Ref 450222 Ccy EUR
Ref 450222 Ccy GBP
Ref 450223 Ccy USD
Ref 450223 Ccy Eur

to

Ref 450222 Ccy GBP
Ref 450222 Ccy GBP
Ref 450223 Ccy USD
Ref 450223 Ccy USD

An offset function can't really be applied as the order of the data is random. My subsequent calculations involve a SUMIFS on GBP, USD etc.

View 1 Replies


ADVERTISEMENT

Match Two Lists With One Common Column?

Oct 17, 2008

I have two lists, let's say:

A B
Eva 1982
Anna 1981
Lisa 1983
John 1980
Steve 1972
...

And

A B
Eva Female
John Male
Lisa Female
...

Can in any way match the above lists automaticly, so the information from List number 2, column B, appears at the correct place in List number 1, Column C? For empty cells, no information is fine.

View 3 Replies View Related

Highlighting Active Cell's Row, Along With Any Row That Shares Same Value In That Column

Sep 25, 2009

Is it possible to click on a cell in column C, and have the wishlist below happen:

That active cell's row is hightlighted.

Any cell in that column that has the same value as active cell is also highlighted.

Plus, any cell in another sheet that has that value it's row is highlighted too.

Example:

I click on C5 in Sheet 2 its value is 45000789 it row is highlighted, this value also appears in C3 in the same sheet, so it's row is highlighted as well. Plus, in sheet 1 in C10 this value appears and it's row is highlighted as well. When any of the values are clicked again the highlight is removed from all parties.

View 9 Replies View Related

Summary Report Of Shares Bought And Sold

Apr 6, 2008

A worksheet called "purchases" contains the list of shares purchased during the period 1.4.5 to 31.3.8. It has four columns :

1. Date
2. Name of the share (name is repeated for every new purchase as it is entered in a new row and is totally independent of the earlier entry of purchase)
3. Qty
4. Sale price

Likewise there is a sheet for sales also. I want to do the following:

a) Find out all the records showing purchases of a particular share and add them up (both quantity and value). And thus, make a list of shares showing aggregate purchase of each share during the entire year. Likewise, I want to do this for sales also.

b) For every share that has been sold within one year after its purchase, apply 20% tax on the profit earned on sale of such share.

Additional information: If 100 shares of A Ltd are purchased on 1.4.5 and 200 shares on 1.5.5 then during the sale of 150 shares of A Ltd on 2.4.6, 100 shares purchased on 1.4.5 and 50 shares purchased on 1.5.5 shall be deemed to have been sold.

View 5 Replies View Related

Common Value But Different Logic

Nov 6, 2011

Although the "Trip Sequence" is same , but I need to separate the Trips if the difference between the "1ST TIME " is more than 3 hours keep the first "Trip Sequence" unchanged , then by either adding a prefix or add 2000000 to the second "Trip Sequence" and 3000000 to the Third, so forth so on until all the rules are applied ( assuming more than 3 Trip Sequences could be also allocated in the database ).

Data Base
TRIP#

1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA3270004
XXX05111835$VV1984GVACDG3270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005

In the above examples , The "Trip Sequence" is same 3270004 but the "1ST TIME" is more than 3 hours difference:-

I want to be able ( ONLY IF "1ST TIME" is in access of 3 hours while the Trip Sequence are same , I want the results as follows:-

TRIP#

1ST TIME Trip CodeCount1st Stn2nd StnTrip Sequence
XXX07411230$AA3270CDGFRA3270001XXX1108400$BBK142LHRAMS3270002
XXX0740705$CC32124AMSGVA3270002XXX01441150$DDA98CDGDUB3270003
XXX10321030$MM1947BRUAMS3270004XXX05101410$VV19132AMSGVA5270004
XXX05111835$VV1984GVACDG6270004XXX11451430$KK31133FCOATH3270005
XXX10471700$KK31132ATHCDG3270005

View 9 Replies View Related

Merging Data In Common

Apr 12, 2009

I have two spreadsheets that have one set of data in each column in common.... simply put it is a number.

From the one spreadsheet, I want to pull the column that is to the right of the number in column and merge it with the other data.

I am trying to figure out a formula that will accomplish this... I have attached a file to show the two spreadsheets and my end result I would like to have.

View 13 Replies View Related

Sum Cells By Common Date

Feb 19, 2010

I have a spreadsheet that lists two columns - impressions and clicks - by date. Sometimes the same date is listed in multiple rows. What I would like to do is add the columns so that I have a total number of impressions and a total number of clicks by each date.

For example my data could look like:
Date || Impressions || Clicks
01/01/09 || 5 || 6
01/01/09 || 3 || 4
01/01/09 || 4 || 7

and I would like it to look like this:
Date || Impressions || Clicks
01/01/09 || 12 || 17

A shortened spreadsheet is attached. The actual one is for the past year with over 1000 rows, which is why I'm hoping someone can point me in an easy direction to do the summing.

View 3 Replies View Related

Finding Top 3 Most Common From A List?

Apr 14, 2013

I am currently trying to organise a student yearbook for my pupils at school. We have given out sheets to students in order for them to nominate their peers for various awards such as 'Funniest Student', 'Most Caring Student' etc.

I am trying to collate all of the data into an excel workbook. I have made two sheets within a workbook, which I have formatted how I want them to look like. I want to insert all the student's responses into one sheet, and somehow, the top 3 most occurring nominations are found and are inserted into the appropriate cells in the separate sheet.

I have attached a copy of the workbook. Due to data protection the student names and tutor groups have been taken out and replaced with the word "REMOVED", yet I will paste them back in at a later time. So feel free to have a play around to see what I am talking about! I have filled the table with some dummy data just so you can see what I mean.

I am not at all sure how I would go about picking out the top 3 most occurring names for each award, but once I can do that, I will be able to insert them into the other sheet. How to automatically produce the top 3 most occurring names.

View 1 Replies View Related

How To Create Third Column With Common Value

Aug 22, 2014

I have two columns(1 and 2), Column two will for each data set have two common values. For most of the data set where column two have the second common value, i can find it based on column 1, but where column 1 differs, i do not know how to get data based on column two.

See attached, column 1 and 2 data. Column 3 I took out one common value, column 4 I just did a IF function to populate blank spaces with common value, but for row 24 - 26 if does not work because column 1 does not follow same logic. Is there a way to populate a third column with one value(can be either of the two common values in column 2(note there will be multiple data sets)?

doc list.xlsx‎

View 5 Replies View Related

How To SUM Data With The Common Header

Aug 3, 2014

I have attached the excel sheet here for reference.

I would like to sum up the values across rows, so long as they have the terms "FQ1", "FQ2", "FQ3" or "FQ4" in the first row.

In other words, for the row "Total Revenue", I would like to sum up the quarterly values, using VBA, since the number of quarters increase with time.

How should I code up a VBA for that?

View 4 Replies View Related

How To Find The Most Common Numbers

Apr 9, 2009

I have 300 number groups and each group has 22 different numbers (from 1 to 80).

Can I find the most common 5 or more numbers in these groups by excel, macro or any program?

for example, let us suppose that 4-15-23-36-45 are the most common numbers and these numbers are in 8 groups from 300.

View 11 Replies View Related

Return A 4th Element From 3 Common Ones

Dec 11, 2007

My main report (Report 1) has part number, serial number, and work order information, among other things I need. Report 2 contains this information as well, but also has a work order item number, which is needed in Report 1. Otherwise, Report 2 has nothing else of interest.

What functions will allow me to get the item number information I want from Report 2 into Report 1?

I have spent a lot of time trying various combinations of VLOOKUP, SUMPRODUCT, IF, etc with no luck. There is probably a very simple (I hope!) thing that I am overlooking.

View 9 Replies View Related

Truncate To Last Common Folder In A Column?

Sep 15, 2014

I have Column A with file paths from the same main directory.

C:UsersSJohn.AJAXDesktopTimeBills.docx
C:UsersSJohn.AJAXDesktopTimeDChargers hick.docx
C:UsersSJohn.AJAXDesktopTimeDChargers raps.docx
C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstadium.docx
C:UsersSJohn.AJAXDesktopTimeDChargersDodgersstats.docx

I would like to find the last common folder along the entire Column (Time),
remove all to the left, and insert E: in its place. Getting:

E:TimeBills.docx
E:TimeDChargers hick.docx
E:TimeDChargers raps.doc
E:TimeDChargersDodgersstadium.docx
E:TimeDChargersDodgersstats.docx

I have already created a VBA Script for other parts of this project, so I would like to add the script to the end rather than create a separate macro. If there is a formula that can do this I am willing to work with that as well.

View 4 Replies View Related

How To Find Common Values Among 3 Columns

Dec 11, 2013

I have a fairly large timecourse dataset and I need to find all common values within all 3 columns. Also, when I find these 'common values' is there a speedy way to retrieve data in the same row that is associated with these values, instead of going back one-by-one and copying and pasting beside the value that the function has returned?

View 10 Replies View Related

Merged Details From 3 Or More Tabs Into Common Tab?

Mar 11, 2014

The file have 4 Tabs (see attached file). The tab called staff, project 1 and project 2 are manually entered. I need to combined some of the information contained in the 3 tabs into "assignment" tabs in order to show the staff workload against the projects/tasks assigned to the individual staff.

In "assignment" tab, I would like to get have pull-down function to obtained the Project 1 and Project 2 "Activities" in Cell Column B & C and automatically placed "start" & "finish" date Cell Column D & F onto the assignment tab Cell Column B, D, E & F

In "assignment" tab, the staff assigned to execute the tasks will also be able to obtain by pull-down function obtained from the "staff" tab, the position Column C, Name in Column F, Job in Column E, Start in column H and Finish in column I onto the "assignment" tab Column B, C, D, E & G

The timeline should show automatically the bar based on the start & finish date. The colour for Task and Staff to be different colour.

View 1 Replies View Related

Find The Lest Common String-value In A Range

Feb 1, 2009

Everything is explained inside the attached WB.

View 4 Replies View Related

Sorting Columns With Common Values

Nov 25, 2009

I have two sets of 2 columns. One column in one set is identical to a column in the other set, but they are arranged in different orders. I would like arrange those two columns side by (matching their numbers) so that I can find the average of the two non-identical columns.

View 2 Replies View Related

How To Add Common Word In Filled Column

Sep 18, 2013

I have a column in excel sheet of more than 1000 different names. I want to add S/o with each name how can I do that ?

View 4 Replies View Related

Formatting Rows With Common ID Numbers?

Oct 23, 2013

I have almost 300-400 records of such data which needs formatting. There are n number of IDs in the first column & corresponding data in columns 2 & 3.

For those rows which have identical ID numbers, I need to fill those rows with some unique shade of color. If you look at the next sheet, I have manually colored the rows & that is my desired outcome. The shade of color does not matter. It just needs to be unique for each ID.

View 2 Replies View Related

Find Out Common And Uncommon Records?

Jan 1, 2014

I have 3 sets of data in three columns A, B and C. Data in Column A is the base data, now i have certain common records in columns A, B and C. Considering the column A as the base, I want to find out in Column

D = Common records in A and B,
E = Records only in A not in B
F = Records only in B not in A
G = Common records in A and C,
H = Records only in A not in C
I = Records only in C not in A
J = Common records in A, B and C

if possible also in Column

K = Common records in B and C
L = Records only in B not in C
M = Records only in C not in B

I have about 5000 records in column A, B and C each.

View 4 Replies View Related

Merge 2 Tables Containing One Common Number?

Jul 25, 2014

I have 2 tables (see attached short sample) and need to merge them. Both tables containing 1 common ID for location (S220) and one common number. I need to fill out Table A (column A-B) with corresponding numbers from Table B (from column E-J). The desired outcome showed in Table C (column O-P).

View 5 Replies View Related

Change The Name Of Common Dialog In 2007?

Oct 22, 2007

Did the name for Common Dialog change for Excel 2007 and if, then what is this new name?

View 3 Replies View Related

Find Out The Most Common Keywords In Column

Oct 12, 2009

In column A I have 50,000 cells, each containing 1 to 10 keywords. For example
A1 = "jobs"
A2 = "jobs in milton keynes"
A3 = "it jobs in milton keynes"
A4 = "sales jobs in milton keynes"
A5 = "well paying brickie work in spain"
etc
etc

At first I was trying to find out the most common keywords in column A, and I used the following code to do so

View 7 Replies View Related

Matching Data From Common Column?

Jul 7, 2011

Table 1

Code:
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June 232350$0.19 http://www.jyxsaw.com/blackrm/index.php?t202id=2676&t202kw=AMWG16=01_0210-3
7/4/2011AMWG16=01BLK25K_USA[a3]*042711Date a Rich Black Man By June

[Code]....

I've got two tables of information here, if you scroll to the far right of table 1 and look at the left of table two, you'll see a common code like:

AMWG16=01_0211-20
AMWG16=01_0212-16

and these entries will appear multiple times as well

AMWG16=01_0212-16

appears twice and the data is different and will need to be added up

How can I:

1. Add up the data on each table with the common string such as "AMWG16=01_0212-16"

2. Take all the data that has been added up and combine the statistics, for example,
when its all done it should show

AMWG16=01_0212-16

in the first column, and then all the statistics from the two tables in the columns to the right.

I'm guesssing...

Since some entries appear multiple times, you will need to sum up all the unique entries for that value. Say:

AMWG16=01_0211-20

Shows up three times, you'll need to sum up all the values and this needs to be done on both tables

THEN

You can combine the data

I'm familiar w/ vlookup, index, match functions, but I'm not quite sure how to put this all together

View 4 Replies View Related

Greatest Common Divisor Of Two Numbers?

Mar 11, 2013

How to find the greatest common divisor of two numbers? I can not use the built in common divisor function.

View 4 Replies View Related

Getting Most Common Value Of Column That Is Result Of Filter

Nov 1, 2013

I'm applying a filter in a sheet that's something like this, after applying the filter it looks like this (Example Item 999)

Item
Inv. Qty
Avi. Qty
Flag
BOX

999
12
9
N
X1

[code]...

I was trying to get the "Flag" Column value, (inside the filter and it crashed)

Code:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
Set rangeFilteredInventory = .Range("Q1:Q" & LRowOnQ)
ControlFlag = .Range("L2").Value
End With

After the filter, it always take the same value.

Code:
With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"
Set rangeFilteredInventory = .Range("Q1:Q" & LRowOnQ)
End With
ControlFlag = InventorySheet.Range("L2").Value

Is there any way to take the most common letter of the result range?

I'm aware there's a average option in subtotal, but I think it's just for numbers.

Code:

With InventorySheet
.AutoFilterMode = False
LRowOnQ = .Cells(Rows.Count, "Q").End(xlUp).Row
.Range("B1").AutoFilter Field:=2, Criteria1:=Project
.Range("D1").AutoFilter Field:=4, Criteria1:=ContractNumber
.Range("N1").AutoFilter Field:=14, Criteria1:=Code
.Range("Q1").AutoFilter Field:=17, Criteria1:=">0"

[code]...

And it doesn't even do the loop.

View 1 Replies View Related

Find Common Values In Two Lists

May 10, 2007

I have two lists, one is 15,000 records, the other 100 records. I want to find the common records between the two.
I am using this formula currently, but is returning an incorrect result--
=INDEX($B$2:$B$11157,SMALL(IF(COUNTIF($M$2:$M$100,$B$2:$B$11157)>0,ROW($B$2:$B$11157),1000),ROW()-ROW($P$2)+1)-ROW($P$2)+1)

this is the record number in M2
BU1HAD80

it is returning
BI1METBRSM
as the common record. This formula is from Joseph Rubin's book F1 Excel Formulas and Functions

View 9 Replies View Related

Put Data In Column B As Common Row For Each Block

Dec 30, 2008

I have a matrix with info of persons of different cities in consecutive rows, where the
city data is in column B. I would like to eliminate columnb B and divide all persons from
any particular city with 2 new rows, their city name and below a field for every column data.

*The number of employees for city would be variable.

Original data (The columns used go from A to E)

1 City_1 Employee_1 Address_Employee_1 Other data_Employee_1
2 City_1 Employee_2 Address_Employee_2 Other data_Employee_2
3 City_1 Employee_3 Address_Employee_3 Other data_Employee_3
4 City_1 Employee_4 Address_Employee_4 Other data_Employee_4
5 City_2 Employee_1 Address_Employee_1 Other data_Employee_1
6 City_2 Employee_2 Address_Employee_2 Other data_Employee_2 ...

View 9 Replies View Related

Common Macros Ready To Use In Every Instance

Mar 16, 2009

i have a couple of macro's i use regular. can these be loaded when i open excel so they are there to run on any workbook i open?

View 9 Replies View Related

Find Common Numbers Between 2 Columns

Aug 26, 2008

I need to find out common numbers between columns. Each column has multiple number entries seperated by a comma. For example:

Column A: "5592,12222" and Column B: "1,2,3,4,5592,123123" and Column C: "3, 4"

I need to find out any numbers that are found in more than 1 column. Hence, the result should be something like "3,4,5592". If no match is found then maybe can indicate by coloring the cell. My data is over 50,000 rows and 30 columns. I need to do this for each row one by one.

View 6 Replies View Related







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