Free Microsoft Excel 2013 Quick Reference

Lookup with multiple rows.

Hi everyone,

I have been trying to auto update a table by looking up information from a table on top. However, for the top table there are multiple entries in the same column, and my look up only checks the first row and ignores the rest. My formula at this point is: =IF(ISERROR(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))=TRUE, "", IF(INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4)=0, "", INDEX($C$3:$H$33, MATCH("X", I$3:I$33, FALSE), 4))). If you can take a look at my attachment, this formula is in the cells I40:W40. Eventually, I will want to populate the entire bottom table with the same formula. Sorry if I haven't explained my problem better, but this is my first post. Please let me know if you have any questions.


Hey,

I am trying to use the database formulas DCOUNTA and DSUM with multiple rows as the criteria. Syntax being: DCOUNTA(database, field, criteria) where the criteria is an expanding range. Normally you can simply have the following DSUM or DCOUNTA delete the preceding values and then arrive at an accurate figure (shown in the example). However, this increase the calculations and size of the spreadsheet dramatically...thus the need for an alternative solution. I have read of several examples using the offset formula to fix this problem, however due to my inexperience I have not been able to implement that solution. Any help or other solutions would be appreciated.

Thanks,
-D

Attached is my sample workbook...

There would normally be 600+ employees with multiple rows per employee.

I would like Cell O3 in the Premium Calculation Worksheet to look at the Premium Contribution Report, and if Row A contains the employee number (A3) AND row C contains "H&D" I would like it to sum row E.

I included the sumproduct formula I tried to put together but I'm getting an error, so I'm not sure what I've done wrong.

The reason I have it referencing "O2" instead of just inputting "H&D" is that O2 could be any number of plans - I have multiple rows with different plans and I need it to pull in all the data.

I hope there is enough info here to tell you what I am looking for...

Hello All:

I can't seem to get the right code to deliver cells from either my Vlookup or Index/Match functions on matching dates & times.

The point of the code is to use a date/time from one list (thousands long), look for a match in another list (thousands long), and pull in data from the column next to it in the same row. I have used vlookup many times for looking up data before, but the problem is that if the Data table has multiple entries on the same date/time (this is certainly will happen for my data) the Vlookup and Index/Match functions return only the first entry, at least how I have it coded right now.

I attached a spreadsheet illustrating what I tried with Index/Match and Vlookup, and also have it reprinted in text below.

Any coding tips to bring in data from multiple rows with identical matching lookup criteria (like date & time) would be greatly appreciated!

Index/Match trial:
DATA TABLE
Timestamp_1 Key1 Motor1 Amount1
7/20/09 1:34:54 AM 105 1 730
7/20/09 1:35:04 AM 103 1 260
7/20/09 2:19:34 AM 105 1 255
7/20/09 2:19:34 AM 103 2 705
7/20/09 2:19:34 AM 105 1 -515
7/20/09 2:19:36 AM 105 1 -220
7/20/09 2:19:41 AM 103 1 -835

CHECK TABLE
Timestamp_2 Key1 (code is in attached spreadsheet & below)
7/20/09 1:35:04 AM is 103
7/20/09 1:35:05 AM #N/A
7/20/09 1:35:06 AM #N/A
7/20/09 1:35:07 AM #N/A
7/20/09 2:19:33 AM #N/A
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:41 AM is 103

Key1 code: =IF(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0),2)=103,"is 103",IF(INDEX($A$3:$D$9,MATCH(A13,$A$3:$A$9,0),2)=105,"is 105","not 103 or 105"))

Vlookup trial:
DATA TABLE
Timestamp_1 Key1 Motor1 Amount1
7/20/09 1:34:54 AM 105 1 730
7/20/09 1:35:04 AM 103 1 260
7/20/09 2:19:34 AM 105 1 255
7/20/09 2:19:34 AM 103 2 705
7/20/09 2:19:34 AM 105 1 -515
7/20/09 2:19:36 AM 105 1 -220
7/20/09 2:19:41 AM 103 1 -835

CHECK TABLE
Timestamp_2 Key1 (code is in attached spreadsheet)
7/20/09 1:35:04 AM is 103
7/20/09 1:35:05 AM is 103
7/20/09 1:35:06 AM is 103
7/20/09 1:35:07 AM is 103
7/20/09 2:19:33 AM is 103
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:34 AM is 105
7/20/09 2:19:41 AM is 103

Key1 code: =IF(VLOOKUP(A13,$A$3:$D$9,2)=103,"is 103",IF(VLOOKUP(A13,$A$3:$D$9,2)=105,"is 105","not 103 or 105"))

Hi Everyone, I'm new on this forum.

We are trying to create invoices by pulling together data from multiple worksheets. We have an "AccountsList" worksheet with account numbers, customer name addr etc. in rows and a "RECEIPTS" worksheet with receipt numbers and account numbers and dollar totals. We can use VLOOKUP to populate the appropriate fields on the invoice sheet by looking up the account number. But when we try to use VLOOKUP to access the Receipts sheet to pull the Receipt number and Amount, we only get the first receipt number, it will not pull them all if a customer number has several receipts.
example:
Cust#702 has 3 Receipts in 3 different rows like this:

Cust# Rec# Amount
702 10123 $725.92
702 10475 $125.21
702 10587 $75.11

All we can get is the first row.

What is the best way to pull multiple rows with the same lookup criteria? Or is this even possible?

Appreciate any help!

Thanks!

I am having trouble using a calculated item with a pivot table that has multiple row fields. The table below shows correctly.

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=
ABCDEFGHIJKLMNO3Sum of Amt Month            4CustomerLocationJanFebMarAprMayJunJulAugSepOctNovDecGrand Total5Customer ALocation 1100010001000100010001000100010001000100010001000120006 Location 2100010001000100010001000100010001000100010001000120007 Location 3100010001000100010001000100010001000100010001000120008Customer A Total 300030003000300030003000300030003000300030003000360009Customer BLocation 11000100010001000100010001000100010001000100010001200010 Location 41000100010001000100010001000100010001000100010001200011 Location 51000100010001000100010001000100010001000100010001200012Customer B Total 3000300030003000300030003000300030003000300030003600013Customer CLocation 51000100010001000100010001000100010001000100010001200014 Location 61000100010001000100010001000100010001000100010001200015 Location 71000100010001000100010001000100010001000100010001200016Customer C Total 3000300030003000300030003000300030003000300030003600017Grand Total 900090009000900090009000900090009000900090009000108000Sheet2 
[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

However, when I add a calculated field for Q1 (using field "Month" =Jan+Feb+Mar) every location is displayed for each customer. Like this:

******** ******************** ************************************************************************>Microsoft Excel - Book2___Running: 11.0 : OS = Windows XP (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutA3=
ABCDEFGHIJKLMNOP3Sum of Amt Month   

Data:

A B C
D
Project 1 BC Mgmt Gov & Comp Falcone
Project 2 BC Mgmt Project Gov
Falcone
Project 3 Prog Mgmt BC Planning Johnson
Project 4 BC Mgmt 1-10 Plan
Falcone
Project 5 Prog Mgmt Metrics
Falcone

I would like to essentially filter all projects that "falcone" owns on
another tab. How would I lookup and return multiple values (details from col
a-c)?

I see how a value can be looked up in multiple rows from: [URL="http://www.excelforum.com/excel-general/709497-vlookup-multiple-rows-with-the-same-lookup-value.html"]; but I need to do this in multiple worksheets. How do I do the lookup in multiple rows and multiple worksheets? Example, I need to see all the pH values taken during a year, and for about 10 years.

I have cells with multiple phone numbers in there with different formats.
When I highlight the column and choose the text to columns option and select
the deliminted option, there is no way to seperate the information due to a
charachter sign ( a box representing the Shift+Enter function). I would like
to place each seperate phone number in a different row.

Hi

I understand how to code to specify a cell in which to enter data into a spreadhseet, when the heading contains only one row.

In the attached example the headings contain multiple rows with merged cells, is there any way of overcoming this so that the entries are placed in the correct cells?

Many thanks.

Andy

Hi everyone,

This one should be a nice one for the experts. Attached please find an example file.

The file is based on a Gantt chart and each project has 4 rows that need to stay together.

I would like to be able to sort the various projects, based on different type of criteria:

1.) project start data (advertising date)
or
2.) project leader and then by start date

As you can see the problem is that I am trying to sort data but the four rows always need to stay together in the same order.

Now, the solution I had thought of was to create some hidden columns, one numbered with:

1
1
1
1
2
2
2
2
The second one with:
1.1
1.2
1.3
1.4
2.1
2.2
2.3
2.4

Then, when applying the sort mechanism based on these columns, the data would be sorted in the correct way.

The problem here is though that as there might be different people working on this file, these numbers might not always be replicated correctly (copying and deleting of rows for example by unexperienced users).

Is there any more elegant way to do this sorting of multiple rows? Besides, I also have merged cells which make life more difficult, but I can remove these, if this helps to get the problem "sorted" ;-)

Thanks a million!
Titus

I've come upon a problem with sorting that I don't know how to
tackle... I have entries in a workbook that I want to sort by a
transaction number, but each entry spans multiple rows. One "entry"
might look like this, for example:

TransID PassengerName Ticket#
leg of travel: Departure Arrival
leg of travel: Departure Arrival

I need to be able to sort by TransID or PassengerName while keeping the
"legs of travel" attached to the correct TransID/Ticket#.

Any Suggestions?
Thanks,
Andrew

I want to do a lookup with several independent rows.

Ex.

General lookup formula is: '=LOOKUP(4,7:7,9:9).
Looking up "4" in row 7, result from row 9.

However, I want to lookup the value "4" in both row 1,3,5 and 7, and get results from row 9, 13, 15, 17.

Note that the rows are not cronologically sorted after another (1,2,3,4,5 etc),
but will be picked random.

?

I have a table of data and am looking to compare a particular row that is unknown with another row.

Example:

. ------ A ---- B ---- C ---- D ---- E ---- F

1 ------ 1 ---- 2 ---- 3 ---- 4 ---- 5 ---- ?
2 ------ 6 ---- 7 ---- 8 ---- 9 ---- 10
3 ----- 11 --- 12 --- 13 --- 14 --- 15
4 ----- 16 --- 17 --- 18 --- 19 --- 20

The value of F1 corresponds with row A and will determine which row to use in the table, eg if F1 = 6 then the row used will be A2:E2. This row will then be compared with another using the Lookup function.

My problem is how do I find which row in from column to use from the value in F1. I've tried using the lookup funtion but I am struggling slightly.

Hope I've been clear enough for someone to answer. Thanks.

Hi Friends,

I have 2 sheets with a matching column "ID".I would like to get the data from Sheet1 and Sheet2 onto a single sheet with all the records from Sheet1 along with the matching data from Sheet2 based on Column "ID".The data in Sheet2 has multiple rows for same ID and I would like to have all these records with the same ID transposed horizontally into a single row.

I have provided the actual data in Sheet1 and Sheet2 along with the desired output data set in Sheet3.

Can any one help me on this Please.

Thanks in Advance

I have seen workbooks with multiple rows of tabs at the bottom of the workbook. How do I make this happen in my workbook? I have some some 12 tabs with the possibility of many more to be added. Instead of them being all in one row and having to scroll to the right to get to the ones I cannot see, I would like them to be in rows so I can see them all.

Any and all help would be greatly appreciated!

Not sure if this can be done.....

I am creating a Spreadsheet with multiple rows of information, I would like one Combo Box, that will link to multiple cells.

For Instance, when you fill in row 1, use the combo box to complete an entry in that row, when you complete row 2, using the combo box, the choice goes to row 2.....and so on.

I think it can be done using formulas, but I don't know how.....HELP!!! :o

Hi,

I'm looking to create an export file that has multiple rows based in part on
whats in each rows of the original.

For example:

- Original file:

I have a list of dates which i want to look up but with multiple conditions. Eg

In Sheet 1 and cell A1, I have 8-Jun-2005. In cell B1, I want to return the
value 17-Jun-2005 because the date falls between 1-Jun-2005 and 30-Jun-2005.
What is the formula for this?

From To Reference
1-Jan-2005 31-Jan-2005 16-Jan-2005
1-Feb-2005 25-Feb-2005 18-Feb-2005
1-Mar-2005 31-Mar-2005 12-Mar-2005
1-Apr-2005 30-Apr-2005 20-Apr-2005
1-May-2005 31-May-2005 11-May-2005
1-Jun-2005 30-Jun-2005 17-Jun-2005
1-Jul-2005 31-Jul-2005 13-Jul-2005
1-Aug-2005 31-Aug-2005 13-Aug-2005
1-Sep-2005 30-Sep-2005 11-Sep-2005
1-Oct-2005 31-Oct-2005 10-Oct-2005

--
help needed

Hello, I need to know how to import data into a single column with
multiple rows. By default, when importing a CSV or TAB txt file, it
imorts data across multiple columns in one row but I need it to go into
one column and multiple rows. I don't have the option on the import
wizard.

Thanks,
Brian

Hi,

I'm looking to create an export file that has multiple rows based in part on
whats in each rows of the original.

For example:

- Original file:
email1@asdf.ca, data, blah, bleh, blech
email2@asdf.ca, data, blah, bleh, blech
email3@asdf.ca, data, blah, bleh, blech

- What I'm after:
email1@asdf.ca, some new hardcoded value, bleck, 33, TRUE, some other val,
etc.
email1@asdf.ca, some new hardcoded value, bleck, 49, FALSE, some other val,
etc.
email2@asdf.ca, some new hardcoded value, bleck, 33, TRUE, some other val,
etc.
email2@asdf.ca, some new hardcoded value, bleck, 49, FALSE, some other val,
etc.
email3@asdf.ca, some new hardcoded value, bleck, 33, TRUE, some other val,
etc.
email3@asdf.ca, some new hardcoded value, bleck, 49, FALSE, some other val,
etc.

Couldn't find a way to specify export formats like this, but maybe I missed
it? Needs to be done in VB or something?

Thanks in advance for any pointers?

Ben

I've been trying for some time but have not been able to get the right code to do what I need. I have a data file with multiple rows of data for multiple parts. This file gets to me as a csv. file. (see attatchment) I had something similar that I found here and was able to tweek to fit my needs, but it was for a sheet that only contained data for a single file on the worksheet (part of code below). The macro I need know has to work on a sheet with multiple parts (see attatchment for example of worksheet).

Sub ImpactDataProcessing_x10()
    Dim FileName
    Dim Title As String
    Dim i As Integer
    Dim x As Integer
    Dim lRow As Long, lLastRow As Long, lCnt As Long
    Dim wkbAll As Workbook
    Dim wkbTemp As Workbook
    
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False

Set newbook = Workbooks.Add(xlWBATWorksheet) 'create new workbook with single sheet chart
    With newbook
        .SaveAs FileName:="TestResults.xls"
    End With
        
'   set sheet name
        With ActiveSheet
            .Name = "SummaryData"
        End With

'   call subprogram to format sheet
        Call SummaryData

Set wkbAll = ActiveWorkbook 'set object workbook needed to compile data

'   Set the dialog box caption
    Title = "Select File(s) to Import"

'   Select CSV files
    FileName = Application.GetOpenFilename _
        ("Comma Separated Files (*.csv), *.csv", Title:=Title, MultiSelect:=True)

'   Exit if dialog box canceled
    If Not IsArray(FileName) Then
        MsgBox "No file was selected."
        Exit Sub
    End If
    
'   Loop through selected files and add to Results workbook
    For i = LBound(FileName) To UBound(FileName)
        Set wkbTemp = Workbooks.Open(FileName:=FileName(i))
'   store the workbook name in variable "temp"
        temp = ActiveWorkbook.Name
'   Moves active sheet to named workbook
        ActiveSheet.Move after:=Workbooks("TestResults.xls").Sheets(i)
        
'   Keep every 10th row of data
    
    Set wsSht = ActiveSheet

    lLastRow = ActiveSheet.Cells(Rows.Count, "A").End(xlUp).Row
    
    lCnt = 10

    For lRow = lLastRow To 10 Step -1
        If lCnt < 10 Then
            ActiveSheet.Range("A" & lRow).EntireRow.Delete
            lCnt = lCnt + 1
        Else
            lCnt = 1
        End If

    Next lRow
        
ExitHandler:
    Application.ScreenUpdating = True
    Set wkbAll = Nothing
    Set wkbTemp = Nothing
    Exit Sub

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub


I need to filter through the name and address of a client list- deleting entires that have a DO NOT CALL listed in C coloum.

I cant attach the file as I have client information in it, but in short I have 3 coloums, with multiple rows

each entry has a name, a phone number, an address, a city, and if they are "OK" or "Do Not Call"

Is set up as such

A -----------------------------B-----------------------------------------C

Name
#------------------------------#---------------------------------"Do Not Call"
Address
City

Name
#------------------------------#---------------------------------"OK"
Address
City

Name
#------------------------------#---------------------------------"Do Not Call"
Address
City

What I need to do is delete the entry if they have "Do not call" in C

meaning, i need to delet the entire row directly above it, the row that has (do not call) and the 2 below it.

Is this possible?

Thanks

Hi there,

I am new to Excel and I suck, after searching for a solution in excel help directory and on the net I decided to post my questions to the excel experts here :-)

It's probably very simple but I don't know how to do it:

I have imported data from a text file to a csv file simply by saving the text file as a .csv. The text file contained emails separated by ";". When I converted to .csv I ended up with the bulk of the emails spread on 1 row and multiple columns (1 email per column) I actually would like to perform the other way around: 1 column and then multiple rows (1 row per email)

So instead of having all data on a single row with multiple columns such as:

email tab A - email tab B - email tab C - email tab X^100

I would like to have data on a single column but with multiple rows:

Email row 1
Email row 2
Email row 3
Email row X^100

How could I do that? A solution either before converting to csv or once in csv format solutions are fine to me...

The purpose of this is to import emails to a database which only read emails in .csv format and displayed by rows, not columns)

Thanks guys!

I have a spreadsheet with multiple rows of people who have the same address. I want to eventually create labels that list all the people at the same address on one label.

Is there an easy way to select all the same rows that have the same address & pull the names into one row?

There are anywhere from 1 to 5 people at the same address.

Thanks for you help!
D