Dcw0405 said:
I'm needing help figuring out how exactly to return values in another table.
For example, I have a table with a column that holds the values "eBay" and
"PayPal". I want to create a new table that shows all the PayPal
transactions, along with the corresponding values in the next rows.
Here is the table I have...
PayPal Bus A Blue
eBay Train B Green
eBay Plane C Orange
PayPal Car D Black
eBay Truck E Red
PayPal Boat F Purple
And the table I want to create should look like this...
PayPal Bus A Blue
PayPal Car D Black
PayPal Boat F Purple
I tried this formula VLOOKUP("PayPal",$B$4:$E$9,2,FALSE) but when I do that
it just returns this table..
PayPal Bus A Blue
PayPal Bus A Blue
PayPal Bus A Blue
Anybody have any ideas?? Thanks!!
This solution requires no formulas at all.
1. Put a header on the row above each column of data - eg HEAD1 HEAD2 HEAD3
HEAD4
2. Move to the row below the last row of data, and shade the 4 cells below
the last set of data
3. Select the headers, all the data and the shaded row below, and click once
in the Name Box (the drop-down list above the "A" label of column A), and
type Table1 (this will give the highlighted range of cells the name "Table1")
4. Insert another blank worksheet into the workbook, or click the tab of an
existing spare (blank) one
5. Save the workbook
6. Click on a cell in column A of the blank sheet, eg A4 (or wherever you
want the top left hand corner of the new table to start)
7. From the menu, select Data | Import External Data | New Database Query,
and when the Choose Data Source box appears, click the Databases tab and
click once on Excel Files* and then OK
8. In the Select Workbook box that appears, scroll down the list on the left
to find your current workbook (it should be there, but if not, use the window
on the right to navigate to the correct drive and folder), and select it,
then click OK
9. The Query Wizard - Choose Columns box will appear, and Table1 should be
highlighted in the left hand section. Click on the > sign in the central
column, and all of the column headings of your table should appear in the
right hand section. Click Next
9. In the next screen, click the heading under Columns to Filter that
corresponds to the PayPal, etc column in your table (HEAD1 in my example).
Then, in the next section to the right, select "equals" from the list box,
and in the next section to the right of that select "PayPal" from the list
box. Click Next.
10. You can choose any sort options you want, or none, then click Next
11. On the Finish screen, click Finish, then click OK on the Import Data
window.
The query wizard will create a new table with the data from your first
table, filtered by your choices. If you right click the first heading of the
new table, you should get options that include Edit Query and Refresh Data.
12. The reason for the shaded row at the bottom of the original table? It
graphically defines the end of the range "Table1", and if you add extra data
rows ABOVE this shaded area, they will automatically be include in the range
called Table1. When you refresh the new table, any new appropriate data will
be included.
Note that your new table can be named in the same way that you named Table1,
and even when the spreadsheet is saved and closed, you can use the same
process from another spreadsheet to access the named tables, and import data
from them into a new table ...
Welcome to the world of SQL queries!
6, put PayPal in F1. Put the following