Systematically obtaining information from worksheets

  • Thread starter Thread starter Leroy
  • Start date Start date
L

Leroy

First, let me say.. I'm not a super user when it comes to
excel, but my users are. You see, I am the Net admin and
my users have the dilemma.

There are two worksheets,

Worksheet A contains:
order_id, order_amount, order_date, billing_cc_num,
billing_cc_exp, account_name, account_address,
account_city, account_state, account_zip, account_email,
account_phone, membership_name and membership_length

Worksheet B contains:
Merch, RptDate, Merch Name, Card Number, Auth Date, Code
Amount

Note: The cc# is the common field in both worksheets.

How do list the information for each matching cc# in
worksheets A & B?

Example if cc# 4003281278xx50xx appears in worksheet B,
how do I pull every instance of the same credit card
number in worksheet A .. along with all other info -
order_id, order_amount, order_date, billing_cc_num,
billing_cc_exp, account_name, account_address, and so on...

Any help would be appreciated.

Thank you
Leroy
 
One possible solution would be to use an advanced filter and a formula,
assume that the info with headers in sheet A has a range of

A2:O200

then you could hide the filter formula somewhere in sheet A, let's
say IV1:IV2, let's say that you get the cc# in sheet B cell E4

in IV2 (leave IV1 blank) you link to the cell and to the first cell on A
that has a cc# (assuming it is C3 with a header in C2) so the formula
in IV2 would be

=C4=Sheet2!E4

(adapt to fit your own data)

now you can record a macro while you apply the filter,
do data>filter>advanced filter, select the range in A,
in the criteria range put $IV$1:$IV$2 where IV2 holds the

=C4=Sheet2!E4

formula (note that IV1 needs to be empty)

click OK

now you could just use the macro you created and attach it to a button
that you can label and then another button that "resets" the filter.
something like

Sub ShowAll()
On Error Resume Next
ActiveSheet.ShowAllData
End Sub

now when you put a new value in the cell on sheet B (sheet2)
you would click the button with the filter code and when you are done you
click
the showall button..
 
Back
Top