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

## Excel 2010 :: Index / Match Duplicate

Jul 18, 2014

I have a victim of the Index-Match duplication problem in Excel (2010). Basically, I have three columns of data, all daily input for the year.

Column 1 = Date
Column 2 = Actual (Units Sold)
Column 3 = Scheduled (Units Sold)

The Date is filled out through the end of the year as is the Scheduled values.
The Actual values are filled out daily.

I need to generate a summary box that reports Actual, Scheduled, and Variance (Actual - Scheduled) for the time periods Daily, Month to Date, and YTD.

My problem is that when I try to return the Schedule value that corresponds with the date of the last entry, I don't know if I am pulling the correct Schedule value since I do not know if the Actual value (that is pulled from the last value in the Actual column) is unique. So I tried using an Index-Match formula to return the latest value (that is the last record occurrence of the value) to my function in order to retrieve the correct Schedule value, but, sadly, it did not work.

I by no means am an Excel expert like many of you, so I may have some questions along the way.

I've attached a sample extraction from my worksheet and included an example of the Summary panel I'm creating.

ActualVsScheduled_problem - Copy.xlsx

## Excel 2010 :: Index Match Using Array?

Sep 13, 2012

The context of this problem is that I am pulling sales data from different outlets to analyse monthly sales. The data from the various outlets are in various formats and the products, even though they are the same throughout all the stores, are named differently. For example, Mushroom Soup can be named as "Soup Mushroom", "Mushroom Soup" or "Cream of Mushroom". This makes analysis really tough.

I first created a table with a list (K6:P6) that had the varying names of the products. This is shown as the first table with 3 rows.

Then I tried to match this list to a range of products sold by an outlet e.g. Electric. (shown in the next bigger table \$D\$10:\$D\$254, ) Next, I used Index to call up and display the sales of that product next to table with list A (\$G\$10:\$G\$254)

This works for some entries but not for all. I get mostly #NA answers even though I do ctrl-shift-enter. Also, the bigger table is in a separate file and I am using excel 2010.

=INDEX('[GON-JUN 2012.xls]gon-jun'!\$G\$10:\$G\$254,MATCH(K6:P6,'[GON-JUN 2012.xls]gon-jun'!\$D\$10:\$D\$254,0))

[URL]

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

## Excel 2010 :: Index / Match With Reference?

Dec 24, 2013

I am currently using the below formula to add values (D6:D225 on sheets Mon, Tue, ...) given the criteria is met in any cells within the two columns across the five sheets (E6:E225 & N6:N225 on sheets Mon, Tue, ...).

When the references to the columns do not randomly change themselves to #REF! it works fine however, it does this often, forcing my to rewrite the formula.

**Why does it do that and is there a way to stop it?

Also, when I do have to rewrite I am forced to do each cell individually to change the number within the quotes. I have tried to use a cell reference there but when I do Excel just gives me a "0" as if there is no data to be retrieved.

I am using Excel 2010 on a company computer with the macros blocked so no VBA.

Code:
=SUM(IFERROR(INDEX(Mon!\$D\$6:\$D\$225,MATCH("2000",Mon!\$E\$6:\$E\$225,0)),0),
IFERROR(INDEX(Mon!\$D\$6:\$D\$225,MATCH("2000",Mon!\$N\$6:\$N\$225,0)),0),
IFERROR(INDEX(Tue!\$D\$6:\$D\$225,MATCH("2000",Tue!\$E\$6:\$E\$225,0)),0),
IFERROR(INDEX(Tue!\$D\$6:\$D\$225,MATCH("2000",Tue!\$N\$6:\$N\$225,0)),0),
IFERROR(INDEX(Wed!\$D\$6:\$D\$225,MATCH("2000",Wed!\$E\$6:\$E\$225,0)),0),
IFERROR(INDEX(Wed!\$D\$6:\$D\$225,MATCH("2000",Wed!\$N\$6:\$N\$225,0)),0),

Excel 2010

## Excel 2010 :: Freezes When Using Pull Function In INDEX / MATCH

Feb 14, 2014

I am using Excel 2010.

My objective is to pull specific values from an external file corresponding to the correct name and year of my choosing. The first way I thought would be best is to use an INDEX/MATCH function. The problem is the external files change names so I would need a method to easily change the source file name from one cell. I then stumbled across the INDIRECT function, but the INDIRECT function will only work when the source file is also opened. I then came across Harlan Grove's pull function which allows you to import data from closed excel files.

So, this is what my formula looks like: =INDEX(pull("'"&G12&"");MATCH(C15;pull("'"&G14&"");0);MATCH(D15;pull("'"&G16&"");0))

Cells G12,G14, and G16 contain the file paths for the ranges. C15 is name and D15 is year.

The problem I have though is that when I try to execute the function, Excel gets stuck. if the code cannot handle large amounts of data. I tested the code with a simple SUM function for a small range from an external file and it worked just fine.

This is the code I am using:

[Code] ...

## Excel 2010 :: How To Create Macro To Insert INDEX And MATCH

Jan 20, 2012

Using Excel 2010, I am trying to create a macro to insert INDEX and MATCH formulas on a sheet that reference Tables on the same sheet.

The formula works fine when entered in a cell where I can point to the Table. Now I need to put the formula in a macro where it will automatically reference the first Table on the sheet.

Here is an example of the formula:

Code:

=INDEX(Table1891034445678912822253536[#All],
MATCH(\$I35,Table1891034445678912822253536[L/I],0)+1,

Just look at that Table name!!

The original file has multiple sheets and multiple Tables on each sheet. No naming convension was used in naming the sheets or Tables.

Can I cycle through all the Tables on a sheet and rename each of them? For example, find the first Table on the sheet closest to cell A1 and rename it Table 1, find the next Table and rename it Table 2, etc.

Is there a way to reference Tables based on an index (similar to sheets)? Is there a Table(1) and Table(2) referencing system? Is it on a per sheet basis or across the entire workbook? That is, can there be more than one Table(1) in a file?

Can I start in cell A1 and find the first instance of a Table and return the name or index of that Table?

## 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)))

## Excel 2007 :: Index Match VBA Multiple Worksheets Many Rows

Jul 30, 2014

I have a workbook that has 5 worksheets. Worksheet1 is the Summary tab, Worksheets 2-5 contains data all sitting under the same headings and formats however vary in row number e.g.

Worksheet 2 - 63000 rows
Worksheet 3 - 48000 rows
Worksheet 4 - 23000 rows
Worksheet 5 - 21000 rows

In Worksheet 1 Column AQ contains a Cost Centre number which I would like to extract the parent description of in Column BI of the same Worksheet and then the child description into Column BJ. Worksheet 1 currently has 16000+ rows and grows daily.The location of the Cost Centre in Worksheets 2-4 is contained in Column A and the Parent Description in Column W and the Child Description in Column Y.I am currently using this INDEX MATCH formula to search all worksheets but as you can imagine it's tediously slow and needless to say sometimes not reporting/updating the values correctly likely because it's frozen.

=VLOOKUP(AQ2,INDIRECT("'"&INDEX(Sheet1!\$A\$1:\$A\$4,MATCH(TRUE,
COUNTIF(INDIRECT("'"&Sheet1!\$A\$1:\$A\$4&"'!A2:A63355"),AQ2)>0,0))&"'!A2:W63355"),23,0) this looks up Cost Centre in Worksheet 1 Column AQ and returns the results from Column W in Worksheets 2-4 to Worksheet 1 Column BI

[code]....

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

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

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

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

"=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 ?

## VLOOKUP / INDEX / MATCH Function: Match Data From 2 Independent Sets??

Oct 8, 2009

I am trying to match data from 2 independent sets, formatted slightly differently so not sure which function would work best for me. From the attached file, I am trying to match the date and time stamp (in cell A1) with that from the other data (in this example in cell E1) and return the data (from cell F1) to cell C1. So basically any date and time stamp before 04/03/09 04:00 will return a value of 44 (this value should appear, therefore in cells C1 - C30)

## Excel 2010 :: Combine Rows Of Data Into One Row

Mar 7, 2013

My business I work for is a collection agency and from time to time our clients send us files that are beyond recognizable for our collection software to understand nonetheless import into our database.

The file was a PDF at first but I have since been able to convert to excel format.

The data isn't too scrambled just scattered throughout rows. I would like to have rows of patient data into one row.

The file after being converted was all in row A but I have since used text to columns to divide into separate rows. However now one patient information is on multiple rows and I need each patient info on one row.

The file looks like:

|A---------|B-------|C----------|
1.John-----|Doe-----|123 Main St|
2.New York-|NY------|
3.11100----|SSN----|Balance Owed|

I would like the data from multiple rows to continue on row 1 like this:

|A--------|B-----|C------------|D----------|E--|F-----|G----|H-------------|
1.John----|Doe---|123 Main St.-|New York---|NY-|11100|SSN-|Balance Owed--|

I am no programmer in the least and have very little to no experience in pivot tables.

This file has about 7000 accounts with patient info spread through 3-4 rows for each set of data.

How I could go about doing this without manually copying and pasting into additional columns on the same row. That will take hours if not days.

## Excel 2010 :: Total Data That Is On Separate Rows

Oct 22, 2013

I have a large amount of data that is from an events file. When I enter it into excel, it almost maxes out the row count (excel 2010) The data basically lists Event, Date/Timestamp, Call ID, and a few other details. Im trying to get a total of specific events and if they were successful or not, so the other columns arent of concern. For instance, there is an event called searchbyphone, a later row in the data will include searchresult found or notfound. The only way to pair these up is by the ID number which is unique per call.

So in the example below, ID 123-45-789a was a search by phone and successful (found). The Date/Time field is down to milisecond. If this were just adding up all the various events and then totalling the searchresult found/notfound, it would be simpler. However, Im only interested in certain events and found/notfound is used for many other events.

Using the data below, what is the best way to pull out an event (say the total number of searchby phone) and then know how many of those were found and not found (when the information needed is on 2 seperate rows and only the ID would be the same?)

To through a wrench into it, there can be several events with the same ID becasue the ID is unique to a call - a caller can search by a few variables. There will be the same amount of found/notfounds though with that ID. (meaning as a caller, I can do a few searches and all of these will be in the event file with the same ID) In this case, the number of requests (events) will match the number of results (found/not found).

What Im trying to accomplish is to get a total of searchbyphone found and not found, So in this example I would have searchbyphone found = 1 and searchbyphone notfound = 0.

ID
Event
Date/Timestamp

123-45-789a
searchbyphone
10/7/2013 12:52:38

987-65-432b
searchbycity
10/7/2013 12:52:39

123-45-789a
searchresult.found
10/7/2013 12:52:41

874-35-123c
searchbyzip
10/7/2013 12:52:42

987-65-432b
10/7/2013 12:52:47

## INDEX MATCH With Rows And Columns As Reference?

Jun 11, 2014

I am trying to work on a index match but can't seem to make it work.

My look up value are on column while the data I want to show and look up array are on rows and still getting 0 results.

Is there any solution ofr this to make it work without altering my look up value & arrays to columns as well?

## Return Rows That Fit Criteria Via Index / Match?

Nov 6, 2013

I have a dataset of a few hundred rows, but will only provide a small sample of rows for this example.

I need INDEX/MATCH to return the correct values for the subset below.

Column A (Forecast Status) - Column B (Account Name)
Commit - Account01 (Row 1)
Commit - Account02 (Row 2)
Won - Account03 (Row 3)
Won - Account04 (Row 4)
Won - Account05 (Row 5)
Upside - Account06 (Row 6)
Lost - Account07 (Row 7)
Upside - Account08 (Row 8)
Won - Account09 (Row 9)
Commit - Account10 (Row 10)

In a new tab, I am creating a 'dashboard' view that will group each of the following:

Commit ONLY Accounts in cell A1 (Formula to be copied down to retrieve Rows 1,2,10)
Won ONLY Accounts in B1. (Formula to be copied down to retrieve Rows 3,4,5,9)
Upside ONLY Accounts in C1. (Formula to be copied down to retrieve Rows 6,8)
Lost ONLY Accounts in D1. (Formula to be copied down to retrieve Rows 7)

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

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

## 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??

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

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

[Code]....

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

## Down Rows Until Specific Value Is Found Within Index / Match Formula

May 14, 2014

My issue concerns going down rows in a data set. Due to the fact that the data set I am pulling from is not standardized, and roughly 10% of the data set has two extra rows of extraneous data below the reference cell (foreign currency balances which I don't need). The point in my formula is the "+2" (down two rows) condition. I needed it so that the "+2" is replaced with a condition inwhich after the reference cell in the INDEX( and MATCH( function is found the command goes down rows until the term "USD" is found. My formula is as follows:

{=IFERROR(INDEX('SHEET1'!\$A\$9:\$O\$10000,MATCH((RIGHT(C7,8)),(LEFT('SHEET1'!\$A\$9:\$A\$10000,8)),0)+2,11),INDEX('SHEET1'!\$A\$9:\$O\$10000,MATCH((RIGHT(C8,8)),(LEFT('SHEET1'!\$A\$9:\$A\$10000,8)),0)+2,11))}

## Formulas By Using VLOOKUP, INDEX, MATCH, INDEX&MATCH Separately

Oct 8, 2009

I have this table

.......A.....B.....C....D
1.....I......a.....d.....g

2.....II.....b.....e.....h

3.....III....c.....f......i

As you can see, the number I has a,d,and g, II has b,e,and h, and III has c, f, and i

I want to make formula that if I make the input g it would return I, f would return III, and c would return III, and so on

I want to make four formulas by using VLOOKUP, INDEX, MATCH, INDEX&MATCH separately.

## Excel 2010 :: Transfer Data Into Form - Multiple Rows Cells

Mar 27, 2014

Every day I receive a csv file of NAMES, PLACES, ADDRESSES, IDS, SPECIAL ID (ETID), ITEMS, QUANTITY, AND OTHER INANE INFORMATION. which is a list of people from places that are ordering item(s) for each ETID)

I CONVERT TO EXCEL BECAUSE I HAVE TO CLEAN THE DATA FORMATTING, AND SPLIT A COLUMN INTO 2 (LAST NAME AND ETID ARE TOGETHER).

I need to put the items into a form, one order per ETID.

I tend to receive on NAME, PLACE, ADDRESS, sending orders for multiple ETIDS.

I don't know the easiest or best way to get the info into the order form. I have designed the form in word and excel.

## Index And Match Function Across Multiple Rows With Repeating Names?

Jun 4, 2014

I have a data set where the row headings repeat a lot. I have 5 headers repeated probably 30-45 times each. Eg. Truck, Car, Van, Tank, House, Car

At the moment I am using the formula...
=INDEX(\$B\$2:\$M\$45,MATCH(\$O\$2,\$A\$2:\$A\$45,0),MATCH(O4,\$B\$1:\$M\$1,0))

which will only return the result of the first occurrence of the cell it is looking for (for eg. I am looking for the values in the cells in columns labelled 'Car', is it possible for it to look past the first occurrence in the data set and find all the values?

## Pull Every Instance INDEX/MATCH Criteria Into Consecutive Rows

Jan 11, 2010

I'm trying to fix a report that I created but can't seem to figure out a way to display the information for a single student.

I've attached a mock report up so that maybe it would make more sense.

So the first tab, 'DIR', I managed to find a way to pull up only the information for a single student that was selected in the drop-down D1 cell. For that I used a simple INDEX/MATCH combo. The student's name would only appear once in the 'Paste DIR' tab, so it was fairly simple to create these formulas.

Now for the 'Paste Outreach' tab, a student appears multiple times in the chunk of information. Right now, the way that I have the report is so that it would just pull the information from each row, regardless of the student. What I would like to do is find a formula similar to INDEX/MATCH, but find every instance when the criteria is met for the INDEX/MATCH. I would like to be able to log each instance a student has an Outreach log entered. Then if I switch the student selected in D1 of the DIR tab, then it would only populate their Outreach Logs.

So if Student Name & Outreach # matches D1 ('DIR' tab) & "90" in "Paste Outreach, then pull row info into rows 7-11 in Outreach.
Find the next instance Name & # matches, and pull that into rows 12-16, etc.

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

## Offset Index Match Formula Slowing Down Excel

Dec 28, 2009

I have the following formula in a cell:

=SUM(OFFSET(INDEX(J:J,MATCH(9.99999999999999E+307,J:J)),0,0,-M9))/M9
When the workbook with this formula is open it slows down other workbooks quite a bit.

If I run code in another workbook without the workbook with the formula open, calculation takes 0.099 seconds. If the workbook with the formula is open, the same code takes 2.24 seconds.

How could I change/replace above formula to average the last n (cell M9 value) cells in an ever changing column.