Multiple variables

[Guest]

Hi all

I have a large workbook containing 10 columns and 10-15000 rows. In column A
I have customername and in column B I have articlenumber. I would like to
make a new workbook where I put inn customername and articlenumber in
C3(cutomer) and D3(article) and have the whole row returned to row 6 in my
new workbook. How do I proceed?

 

[Bob Phillips]

=INDEX(Sheet1!A$2:A$200,MATCH(1,(C3=Sheet1!$A$2:$A$200)*(D3=Sheet1!$B$2:$B$2
00),0))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

and copy across the columns

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Guest]

Thank you for you answer. The thing is that I need it in VBA since I am to
use it togheter with


http://www.microsoft.com/communitie...xcel&mid=5564d9b1-4849-4834-9515-d0fbeabf4ec3

I have 35 weeks up to now and finding out how many article A customer B have
sold this year is a real bitch. ;-)

Lupus

 

[Harald Staff]

You've parted your data per week, and now you can not part it per customer
or per article insteaad. What you really is a database solution, it is
probably pretty easy to set up if I understand you scenario right.
Considering how much time you spent and will spend on this, making, or
having a database made for you, will pay off in a short time. My 0.02

HTH. best wishes Harald

 

[Guest]

I get the data each week from the main office of my customer, so I haven't
used any time parting the data. The workbook with the lookup functions will
be used by other people in my organisation ass well. Some of them have just
got the basic computerskills so an easy to use "program" will be the best
solution.

Lupus

 

[Harald Staff]

Ok, rephrase: The data is already parted per week, so it is very hard to
part per customer or per article across weeks. What you imo should have is a
system that adds your new weekly data to The Big Database Table, from which
extracting, grouping, pivoting and subtotalling is very easy -that is what
databases do.

Both operations can be made as a very simple "select from a list and click a
button" application, standalone or excel based. I make things like this for
a living and I know what they can do. (And I probably don't live in your
country, so I'm not trying to sell you anything ;-)

If you really want VBA that opens all workbooks in a folder, copies all rows
that matches X criteria, that is of course possible.Record a macro while
setting Autofilter to your criteria in one of your data tables, and you will
have a good start.

HTH. Best wishes Harald