How to take matching data from 2 columns and put in the same row?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

First off, i'm not too familiar with alot of functions and commands within
Excel yet, so if anybody has help, i'd appreciate it if you talked to me like
i was a complete idiot!

Every month i extract data from MS Great Plains into an excel spreadsheet.
The data is a list of customers (Column A - about 740 of them) and the
amounts of our materials that they use every month (Columns B, C, D, etc. -
only 1 item number, so this column is strictly quantities of that item). From
month to month, the list of customers is going to vary slightly based on if a
new customer is added, or previous customers drop out. When this happens,
i've been manually inserting blank rows so that the data in a single row is
always going to be the quantity that a single customer orders each month. As
the list is 740 customers long, it is a pain in the ass to sort through it
manually and match the new data from a current month to the rows where that
customer already has data from previous months. There must be some function
or filter that can do this for me each month. the list is sorted
alphabetically by customer, and when a new customer is added they must be
sorted alphabetically too which is why i've been inserting blank rows.

If anybody would like an example of the sheet i would be happy to supply
one. Thank you in advance for any help on this.
 
I think I understand your problem. I'm assuming that when you download the
info from Great Plains every month - it goes into a separate Excel workbook
(which will be Book2 in the examples below). And you want to take the data
from this workbook and post it to your summary workbook in a new column.
Also, I'm assuming you want to match your customers up by name (customer
numbers would be better as they are unique - any lookup function will get
tripped up if the "key" is not unique)

If your customer names are in one column, you could enter the following in
your summary spreadsheet (assuming the data for Feb is in cells A1:B2 of
Book2):

A B C
Name Jan Feb
McGuire, Carol 45 =IF(ISERROR(VLOOKUP($A2,[Book2]
Sheet1!$A$1:$B$2,2,FALSE)),0,

VLOOKUP($A2,[Book2]Sheet1!$A$1:$B$2,2,FALSE))
Menke, John 15


If your names are in separate columns you could do the following (when you
enter or edit this formula, you must hit CNTRL+SHIFT+ENTER) (assuming Book 2
ColA = Last Name, ColB=First Name, ColC = units)

A B C D
Last Name First Name Jan Feb
McGuire Carol 45
=IF(ISERROR(MATCH($A3&$B3,

[Book2]Sheet1!$A$1:$A$2&[Book2]

Sheet1!$B$1:$B$2,0)),0,INDEX

[Book2]Sheet1!$C$1:$C$2,MATCH

(A3&B3,[Book2]Sheet1!$A$1:$A$2&

[Book2]Sheet1!$B$1:$B$2,0)))
Menke John 15


Then copy the formula down for the rest of your spreadsheet. If you are not
planning on keeping the download from Great Plains as a source for these
formulas, then, after you enter the formulas, select the entire column, click
copy, then go to edit/paste special, and select values to hardcode the
numbers.
 
Back
Top