Excel 2010 :: How To Match 2 Columns And Retrieve Data From 3rd Column

Jan 16, 2013

How to match 2 columns in excel and retreive data from the 3rd column ?I have an example here as to what I want to do..

ColA ColB ColC ColD

niki delhi neha
vinay mumbaihardik
kapil bangalorevinay
neha patna
pooja goa
hardik kerala

Here is what I want to do for the above.

ColA and ColB contains a standard information which is supossed to be my reference. ColC contains my queries for which I need information about their place in ColD.

So I need to match ColC with ColA, so as to retrieve the matched data (between ColC and ColA) from ColB to ColD. Following is the way I expect my result to be..

ColA ColB ColC ColD

niki delhi neha patna
vinay mumbaihardik kerala
kapil bangalorevinay mumbai
neha patna
pooja goa
hardik kerala

I received an answer in that link

"=INDEX($B$2:$B$6,MATCH($C2,$A$2:$A$6,0))",

Which when tried, surely worked a few months back. I am now using excel 2010. I tried the same again, but this time it does not work for me. Is there something else to do which has been changed in the new excel 2010 ?

View 3 Replies


ADVERTISEMENT

Excel 2010 :: Match 1 Cell And Column In Sheet 1 To 2 Columns In Sheet 2 Return Data From A 3rd

Jul 23, 2012

I have 2 workbooks in Excel 2010, each contain just 1 sheet. (see attached) I need to compare on sheet 1, cell D1 and column A:A (this column will be much longer), with the data in columns C:C & A:A on sheet 2, if a corresponding match is found, the data contained in column D on the same row on sheet 2 is written to the cell with the matching data in sheet 1.

My attempt is in cell D2 on sheet1.

View 5 Replies View Related

Excel 2010 :: Move Part Columns Of Data From 6 Columns To Form 1 Large Column In Column A?

Jan 31, 2013

I have a large spreadsheet converted from pdf whose data still appears in A4 reading format.

I need to move part columns of data from 6 columns to form 1 large column in column A.

For example, move range B8 to B76 beneath range A8 to A76 and range C8 to C76 beneath that etc, page by page working through all 270 pages !

Also need to delete unnecessary 'page headers' throughout as in rows 2-6

View 2 Replies View Related

Excel 2010 :: How To Take Data From Multiple Columns And Add Them Onto Single Column

Mar 8, 2013

I have a long list of data with many columns and I'd like all the information to be in one column without manually copying and pasting each column and adding to the first column. The data has different amounts of rows and columns as well. An Example is below. I'm using Excel 2010. Is there a formula or something for this? This isn't the data I'm using but just an example since I do this frequently.

Data Looks like:

54654
31233
42343
51234

66968
43252
54657
63253

[code]....

Would like to look like:

54654

66968

79282

91596

68185

31233

[code]....

View 5 Replies View Related

Excel 2010 :: Column And Row Match And Index

Oct 3, 2013

Whatever formula I try is returning an error of #N/A

I have two worksheets the first with transport data - customer,collection point, delivery point, haulier and number of pallets - all this information is in columns; the second sheet with the rates show haulier, customer, validity, delivery point in columns and then pallet numbers run across a row with rates tying in underneath. i have simplified this below.

I am just trying to reference all the data and work out what the cost for the variables would be in the example the cell i'm trying to find is H8 (320) -

I have already tried the following and several modified versions:
=INDEX('RATE LOOKUPS'!$E$6:$I$9,MATCH(1,('MASTER DATA'!A:A='RATE LOOKUPS'!C:C)*('MASTER DATA'!B:B='RATE LOOKUPS'!B:B)*('MASTER DATA'!C:C='RATE LOOKUPS'!D:D)*('MASTER DATA'!D:D='RATE LOOKUPS'!A:A)*('MASTER DATA'!E:E='RATE LOOKUPS'!E5:I5),0))

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

View 6 Replies View Related

Formula To Match Text In Column And Retrieve Adjacent Column Value?

Mar 5, 2014

I need a formula to run down a column DCapture.JPG (starting at 142), when it finds the last entered value it needs to display the corresponding value from column J into cell AA21.

If you see the attached photo, the last entered data in column D would be 1, AA21 would be saying -30 (J205)

Capture.JPG

View 6 Replies View Related

Excel 2010 :: Retrieve Complete Accounting Cell Value From VBA

Nov 11, 2013

Can I retrieve, from VBA, the exact value of a cell?

If in A1 I write 0.12348 with general, number or text format, from vba with MsgBox ActiveSheet.Cells(1, 1) I obtain 0.12348.

If in A1 write 0.12348 with currency or accounting formats, from vba with MsgBox ActiveSheet.Cells(1, 1) I obtain 0.1235.

Naturally, I need retrieve complete values from cells with format accounting?

My Excel 2010!

View 2 Replies View Related

Excel 2010 :: Search Column For Match If Found Copy Cell To Next Vacant Row

Jul 8, 2014

I have a file which has in excess of 12,000 rows of data in 5 columns (sample file attached with fake data). The five rows are:

"First Name" "Last Name" "Name" (uses CONCATENATE to combine column A & B) "Email" "Date Attended"

There will be duplications in the list as people will have attended more than once over the years.
What I want to do is search through the email addresses (Column D with D1 being the header) and where there is a duplicate email address copy the cell to the right of the duplicate (F#) into the next available cell to the right of the first occurance and then delete the row with the duplicated email address.

I am on Windows 7 and Excel 2010

View 4 Replies View Related

Excel 2010 :: INDEX MATCH Only Returns First Value Instead Of All Matching Values In Selected Column

Jul 18, 2014

Excel 2010: I'm working with a sheet where I would like to get accurate results on the make and model of cars sold in a given week. The workbook contains several worksheets each representing weekly sales numbers for different vehicles.

The Make-Models tab has the lists used to create the drop-down selections.

The Sales tab allows one to create an ad hoc report on the total number of vehicles sold, by Make and Model. Cell B2 is named 'SelectedModel' and used in the formula found in column E, Total Sold.

Problem: When I select Honda, for example in B2 of the Sales worksheet then select Accord in cell B3 of the same. The results returned in E2, E3 and E4 are 2, 3 and 4, respectively, instead of 8, 3 and 7.

2 Honda Accords were sold on Monday and 6 on Tuesday of the same week. My formula is only returning the first matched value of 2 whereas I would like it to return 2+6, 8 for week 1. Same goes for other weeks.

Select any other make and model and notice the problem follows. I believe the error is due to incorrect usage of the formula or incorrect formula altogether.

IF(ISNA(INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0))),0,
INDEX(Week1!E:E,MATCH(SelectedModel,Week1!B:B,0)))

View 9 Replies View Related

Excel 2010 :: How To Filter / Sort Data Based On Partial Match Of Data In Cell

Apr 16, 2013

I am using Excel 2010. I am a novice user.

I have a lot of data to filter / sort. I want to initially to create a filter for a column of data - which has the format similar to hierarchical paths to files. The data is a mix of text/numbers. e.g.

pathA/path_X/path_Y/path_Z/lso0_rxs_reg_254__5_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_253__5_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_255__5_0/d

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

Doing an alphabetical sort of this date would return the following order. As you can see while each strings in unique - there are many instances where they are simialr - if you ignore the unique numeric values at the end of the string.

pathA/path_123/path_456/data_out_reg_0_0/d
pathA/path_123/path_456/data_out_reg_17_0/d
pathA/path_123/path_456/data_out_reg_4_0/d
pathA/path_X/path_Y/path_Z/lso0_rxs_reg_230__6_0/d

[Code] ......

So what I want to do is to create a filter for the strings - but ignoring the numeric bits at the end i.e.

reg_[0-9]+_+[0-9]+/d

The strings are obviiously of varying length and the number of hierarchical paths is different, so I can't split string on "/".

Similarly folder paths names can contain "_" so can't split string on this either.

As I don't know how many "/" or "-" instances there will be in the string I don't believe I can use the find function. Also as the amount of number will be different i don't think I can use =right(a1,X) either.

I may be able to search for the pattern above - as this is probabay unique - so maybe it's something like the following pseudo code:

Function GetString(txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern = "reg_d+(_)+d+//d"
GetString = .execute(txt)(0)
End With
End Function

If I do require VBA code - how do I then use this for creating a column filter? Or will I have to extract the filtered data first from the column (and its associated row data) into another worksheet to use?

Once I have the filter in place I want to create tables using the filtered data - so for example each column value above has a lot of associated data values in each row e.g

26 pathA/path_123/path_456/data_out_reg_0_0/d
32 pathA/path_123/path_456/data_out_reg_17_0/d
8 pathA/path_123/path_456/data_out_reg_4_0/d

So my table would show the name "data_out_reg" and the range of values 8-32

View 1 Replies View Related

Excel 2010 :: Retrieve Photo From A Folder And Paste It Into Cell

Apr 20, 2014

Excel 2010, Windows 8.1. I want to retrieve a photo from a folder and paste it into a cell. I have the paste and format process figured out but I'm having trouble putting the file path together to retrieve the photo. If I use the full path, the statement below retrieves "IMAG1234.jpg" and puts it where I want it.

ActSheet.Pictures.Insert("C:UsersEljafeDocuments1-QCReport_PhotosIMAG1234.jpg").Select

But if I have a different photo file name as a string in a cell (e.g. "IMAG5678.jpg") what is the syntax to attach it to the file path? i.e

ActSheet.Pictures.Insert("C:UsersEljafeDocuments1-QCReport_Photos") & "IMAG5678.jpg".Select '(which of course doesn't work)

So, I have a path but I want to attach different photo number to the path from a sting in a cell.

View 2 Replies View Related

Excel 2010 :: Index - Match With Data In Rows

Jan 7, 2013

Using Excel 2010.

Rows 1 and 2 (range: A1:CM2) within Spreadsheet X contain the range of data that I need.

Within Spreadsheet Y, Row 6 will change monthly and will be input by the user. The value in Row 6 equals the data in Row 2 in worksheet X.

I need to put a formula in row 4 of spreadsheet Y that returns the value of Row 1 in Spreadsheet X.

Unfortunately, I cannot change the spreadsheet layout, otherwise I could do an HLOOKUP formula using Spreadsheet Y Row 6 and Spreadsheet X Row 2 and be done with it. I assume that I need to use an Index-Match formula, which I am not as familiar with. In addition, all of the online support I have found explains Index-Match with data in columns, not rows.

View 1 Replies View Related

Excel 2010 :: Using Data From Two Cells To Match Data Within Another Sheet

Dec 7, 2012

I have a workbook with data on sheet1 and a summary on sheet 2. I want to use two drop down boxes on sheet 2 (B3 and B6) to look up matching data from sheet 2. What formula is best to enable these two look_up values to be used? I saw an example where the two values were separated by a comma within {} i.e. {B2,B6}, but only got an error when I entered the formula into my Excel 2010 sheet.

I have previously used VLOOKUP, but this comes back saying I have to many arguments. I can attach the workbook if that makes it easier to explain.

View 1 Replies View Related

How To Match And Retrieve Data

Feb 4, 2013

I need to match and retrieve data.

If cell A25 matches B3:B19, B25 should pull the Name and C25 the Cost.

I have attached a spreadsheet.

View 3 Replies View Related

Excel 2010 :: Returning Value From Third Column Based On Two Other Columns

Jun 24, 2012

Basically; there are three main columns in the first worksheet (lets call it "Main Data"): OrderNumber, TaskName, SignOffDate with data listed as follows. The actual spreadsheet has hundreds of order numbers but i'll keep it simple and lets go with two.

OrderNumber
TaskName
SignOffDate

1
OED
01/05/2012

[Code] ........

What I am trying to do; is sort this data in a second worksheet (lets lable it "Output") so that the sign off dates for each task; for each order; are listed within 1 single row. Ie:

OrderNumber
OED
CTN
FAD6
RFS

1
01/05/2012
17/05/2012
22/05/2012
02/06/2012

2
03/05/2012
19/05/2012
27/05/2012
02/06/2012

There are many orders in the main data; and I'm not sure what to do exactly to return the sign off dates for each task for each order without creating separate worksheets for each task name; then using vlookups to find each date.

An order may have a sign off for all task names, or none at all. In addition to this; they may not always be in the same order as listed above.

I'm using Excel 2010.

View 5 Replies View Related

Excel 2010 :: Comparing Multiple Columns Of Data (Last Name / First Name)

Aug 8, 2012

I'm using Excel 2010 on Windows 7.

I have 2 worksheets. One has Employees and the devices they have. Last name, First Name, Device each in their own colulmn. Many have more than 1 device so they have multiple entries on seperate rows.

Another worksheet has Employees and thier location. Last name, First Name, Location. Again, all on seperate columns.

It would look something like this

Sheet1
Lastname
Firstname
Device
Johnson

[Code]...

So I'm tasked with combining them into 1 sheet with last name, first name, device and location. The issues I'm having are:

1) A team member could have multiple devices
2) A last and/or first name can appear many times, so a simple Vlookup against lastname won't work - it has to somehow also compare against both.

View 6 Replies View Related

Excel 2010 :: Delete Row With Duplicate Data In 2 Columns?

Apr 21, 2014

Basically, I have a sheet and I would like to delete the entire row if the data in column G is the same as that in column H. The data is text if that matter. I've tried to figure out the VBA code for it, but my knowledge is severely limited. The spreadsheet is excel 2010.

View 3 Replies View Related

Build Index-Match Formula To Retrieve Number From Data Sheet?

Aug 5, 2014

I'm trying to build an Index-Match formula to retrieve a number from a "data" sheet onto a "summary" sheet.
In example spreadsheet, I need to retrieve the stock price on a specific row from "data" sheet, and as an alternative, the most recent price.

View 6 Replies View Related

Match And Align Data In Excel Columns And Rows?

Aug 21, 2013

In an Excel sheet; I am trying to match and align data in column C to data in column A, but it is essential that the data contained in the entire row (coln B thru to coln W) moves when Column C is matched and aligned with column A.

Sample workbook attached.

View 9 Replies View Related

Excel 2010 :: Transposing Data With Blank Rows In Columns

Feb 10, 2014

We utilize large data sheets that can be as large as 300K in rows and 10 to 15 columns wide. Because of how we receive the data, we are forced to manipulate things so that all matching data for a record ends up on a single row. (e.g. Record#, Document Type, Husband Name, Wife Name, Wife Maiden Name, Etc.)

Right now here's how the data is received:

a
a
a
b
b
c
c
c
c
d
d
d
e
e
e
e

Using two vba scripts, we first separate the data with row spaces between the unique data as follows:

a
a
a

b
b

c
c
c
c

Then with another script, we transpose the data as follows:

a a a
b b
c c c c
d d d
e e e e

When we transpose the data, the end result starts at the top of the page and go down eliminating the original blank rows. Not a huge issue but I would like to be able to maintain the original data format of the rows so that the data matches the original sheet line for line. The end result would give me the data as follows...

a a a
b b
c c c c
d d d
e e e e e

where the vertical gaps between the letters matches the original rows. Like I said, not a huge issue since we can rejoin the transposed data to the original data fairly easily. But it would be nice if we could end up with the above format for speed sake.

The two scripts we use, one-to insert the rows and two-to transpose, take a very long time to run with the transpose script taking the longest by far. On a 30K row sheet, it will take on our systems around 30 minutes to transpose and about 15 minutes to insert rows. Because we have several columns that need to be transposed, a 30K row sheet will take at least 2 hours to complete. A 300K row sheet, that will take 10 to 15 hours to complete.

Is there any way to speed up the scripts either by upgrading to a faster CPU and or writing the scripts to preform faster?

My preferred solution would be to write (have) a formula to preform the transposition that gives me the results as noted above since formula's run so much faster than vba. Is this possible? I have tried all kinds of formulas and can not come close and of course the straight transpose function does not give me the solution I need as noted above.

I have enclosed an excel 2010 spreadsheet with 10K rows of data in rows along with the scripts I use (nothing sensitive here). The tabs at the bottom shows you the data before I transpose, then the data after it has been transposed . To speed up the scripts, I have stripped away the all the rest of the data from the original sheet except just what I need to transpose at one time. Once that is completed, we then re-join the transposed data with the original sheet. The six digit number you see to the far left of the data is the record ID number from the original data. We use this to rejoin the transposed data with the original data so that we know everything is back where it should be. (Note: The insert rows script is run on the original data and not the data you see on the enclosed spreadsheet. That is the only way we can generate unique rows with matching ID numbers. We arrive at this by taking the original data, concatenate the record ID with the column we want to transpose and add a # between the two so that we can break things back apart after the transposition using the text to column function using the # as the separator.)

The sheet I have attached is in the 2010 Macro Enabled format...(xlsm format). We use the xlsb (binary) format for the data to reduce the file size as our normal procedure and run the macros from inside that format. Changing from the xlsx to xlsb format did seem to speed up the scripts a bit and greatly improved the file performance as a whole e.g. saving and loading.

One thing I have done to speed up the scripts is to strip all the data away that is not needed for the transposition. That did work but only a marginal amount.

We are using windows 8.0 with 4G memory and your basic processor speed...e.g. nothing fancy.....just your basic stock computer. Nothing else unusual is installed or running on the computer or at the time the scripts are running.

For those of you that process large sheets, how much of a performance upgrade will we see in processing our scripts by either upgrading memory to 8G (or more or much more) and or getting a faster processor? Or have we reached the maximum script speed already? Or is this a limit to Excel.

One other issue to note: As I stated above, on the 30K row sheets, not a super problem with about 2 hours needed to run the scripts on all the data on the sheet. But on the 300K row sheets, it can take 12 or more hours to run and there are times when things 'lock up' running the scripts on sheets this size.

View 5 Replies View Related

Excel 2010 :: Macro - Move Data From Rows To Columns

Dec 13, 2011

I am using Excel 2010 and need a macro that can convert data from rows to columns. I have read several posts about this subject but have no experience with macros and don't know how to change the macros to fit my scenario.

Here is what I currently have:

Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008........FL
11111......2000........FL
12121......1999........GA
33222......2000........AL
33222......2011........AL
33222......2001........MS

Here is what I need it to look like:

Account...Vehicle1...Loc1...Vehicle2...Loc2...Vehicle3...Loc3...Vehicle4...Loc4
11111......2008.......FL.......2000........FL
12121......1999.......GA
33222......2000.......AL.......2011........AL......2001.......MS

There are up to 4 vehicles/locations per account number, and I need 1 account number per row (the dots above are for spacing only and not part of the actual data).

I could do this manually but because I have so many rows of data it could take days or weeks. Is there a macro out there that can do this??

View 3 Replies View Related

Excel 2010 :: Copying Data Form Columns To Rows?

Mar 27, 2012

I need something that will take data from columns in one Spreadsheet and put in difference cells in a row. I know this could be done with recording a macro but the number of column will never be constant.

Below I attached examples of the Spreadsheet

Financials SpreadSheet
Need to have the data in column B to F put their respective cells in row in the
Master Spreadsheet
So we would have 5 rows.

Excel 2010ABCDEFGH5Job Number17542000250030003500Total6Date Booked01-Mar-1215-Mar-1215-Mar-1223-Mar-1223-Mar-127SalespersonJames ThorntonHoward StandenHoward StandenIan BullimoreSylvia Walton8AdvertiserNestleTalkTalkLloyds BankSkodaHonda9ProductNature ValleyBroadbandMortgageApril

[Code]....

View 6 Replies View Related

Excel 2010 :: Moving Data Organized By Columns To Rows?

Nov 26, 2013

import it into to Stata to do statistical analysis. I always receive spreadsheets like this:

country
1991
1992
1993
1994
1995
1996
1997
1998
1999
2000

[code].....

and I have to manually reorganize it like this to import into Stata:

country
year
value

Benin
1991
20

Benin
1992
254

[code].....

Is there way I can quickly design a macro to do this? The problem is that I generally have a list of about 60 countries, and years from 1991-2011. So, it's really time consuming copying the column of data corresponding to the year, pasting below, repasting the list of countries and the years...then again..then again...then again...I'm using Excel 2010.

View 7 Replies View Related

Find / Match Data From Columns And Copy Value To Column 4

May 25, 2013

I am going to explain the issue with a photo linked below

Capture2 | Flickr - Photo Sharing!

Basically i want to match data from column one with data from column 3 if true then copy the data in column 2 to columns 4 in the same match row.

View 9 Replies View Related

Excel 2013 :: Highlight Cells If Data In 2 Columns Match

May 27, 2014

Please see the attached example. I'm trying to highlight cells only if 2 values in the row match 2 values in another row. If a book title/author combination matches that of another title/author combination in a different row, it would highlight. Some alternate rows will contain different titles by the same author, and some rows will have like titles by different authors...but they should not highlight.

The only situation where highlighting would occur is if the title/author pairing appears in another row.

I'm trying to decide if this would be a conditional formatting/highlighting rule, or if it would be a macro/vbs?

I'm using Excel 2013.

Pairing example.PNG

View 14 Replies View Related

Excel 2007 :: Match Data In Two Columns And Return Header

Apr 20, 2012

I have two columns with team abbreviations, one simply says whether they are AL or NL the other is the result of a search with multiple occurrences of the team name. I need to match the long list with the short and put the column header AL or NL in the adjacent cell.

I'm using Excel 2007

The last formula I tried was =INDEX($AY$1,MATCH(BB2,$AY$2:$AY$15,0),0)

The screen shot actually just part of the sheet copied since the screen shot look like a broken html page Ok I give up. I copy and paste a jpg it turns into code, I copy and paste the spreadsheet and formatting vanishes, I don't have a URL for the picture... I did look at FAQs and didn't find picture rules but I will look again. Meanwhile. . .

It's just six columns of data. The short list is in AY from AY2 : AY15
The long list is in BB from BB2:BB505
The column header "AL" is in AY1 and NL is in AZ1

View 2 Replies View Related

Excel 2010 :: How To Aggregate Data From One Column

Mar 2, 2012

I am using Excel 2010.

I have a large spreadsheet with brands and volumes.

Many of the brands run the same processes, so I can consider them as one.

For example:

Brands - Volumes

A--------10
B--------16
C--------18
D--------20
E--------16

Imagine that A and C are basically the same. So the value that I really care about it (10 + 18) = 28
B and D are the same, so I care about (16+20) = 36

The output I need is:

A&C - 28
B&D - 36
E --- 16

Can I use SUMIF to basically say: Sum this IF =A OR or =B ?

Can I apply an IF function with concatenate to merge the A's with C's and B'd with D's?

The spreadsheet is large and this process will apply to many different groups of brands.

View 2 Replies View Related

Excel 2010 :: Conditional Formatting - Highlight Values Based On 2 Columns Of Numerical Data

Apr 12, 2013

I'm having issues with Excel's 2010 conditional formatting. Seems easy to use, but I'm trying to highlight values based on 2 columns of numerical data. Example:

Column F:
6
6
14

Column L:
3
NA
17

I would like Column L to highlight values that are greater than Column F in green. If they are less than Column L then highlight them in red.

Seems I was able to do this with Excel 2003, but I don't understand the 2010 version.

View 3 Replies View Related

Excel 2011 :: Can't Get Index Match Max Functions To Work Sampling Data From Multiple Columns

Jan 29, 2014

I have 3 calculations I would like to make based on data in the spread sheet and I can't seem to get them to work with data from the two separate columns.

I tried a few of the index match max formulas I found here and could only get them to work with one column of data.

I have the spread sheet attached and the 3 calks I want to do are blank on the bottom.

I am using Excel 2011 for Mac

View 7 Replies View Related

Excel 2010 :: Post Data Between Two Dates In Selected Column

Nov 9, 2013

I have attached link to a resource planner I have cobbled together and am stuck on the last piece of code. The "Assign Project" button needs to take the project and resource from the drop-down lists and populate the column of that resource with the project name between the dates that have been selected.

Resource Planner: [URL] ....

I'm using Excel 2010 and Windows 7

View 6 Replies View Related







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