Macro, VLOOK, or "IF" formula?

Hi,

I am the epitome of "novice" when it comes to this. I have spent way too many hours on this, hopefully a master can assist. I have attached an example.. the true sheet has 200+ columns & 450 rows.

Here's what I need:
1) move specific data from sheet 1 to sheet 2 in vertical format - based soley on cells H8:FU450 (must be filled and greater than $0.00).
2) data moved to sheet 2 vertically:
-- Activity ID (column C)
-- Vendor ($E3$)
-- Market (H6-H200)
-- Price (if not blank & greater than $0.00)

If an "IF" formula is best, how do format it so it doesn't enter a "FALSE" and simply moves to next row?

I want a Macro to perform this, but I've failed miserably. I'm throwing int the towel & seeking assistance.

Any guidance is greatly appreciated!

JD0711

Could you update your sample workbook to reflect what you would like the outcome in VERTICAL to be?
I'm a litte confused as to the market.

Thanks!

rvasquez,

Thank you for responding. Attached is another file with a few edits.

Basically, I want sheet2 to pull data from sheet1 soley based if cells H13-K15 (in the sample) are not blank or greate than $0.00. I set formulas in sheet 2 columns to describe what I need. I'm hoping to avoid the formula approach as there will be over 72,000 data points!

In sample doc:

Sheet 2 has four columns:
1) ID - if H8-H15 have values greater than $0.00, paste corresponding C data (C8-C15)
2)Vendor - if H8-H15 have values greater than $0.00, paste $E10$
3) Market- if H8-H15 have values greater than $0.00, paste corresponding Market above (H6-K6)
4) Price - if H8-H15 have values greater than $0.00, paste it.

I hope this clarifies things for you. I'd like to make it as easy as possible to understand since you're being kind enough to give me a hand.

JD

Hello there,

I think I understand what you are trying to do so here goes a shot at it.

Attached is your original workbook updated to include a macro that I believe accomplishes what you are looking for. To run the macro, open the workbook, press Alt+F8, select the CopyInfoToSheet2 and select the Run option.

To view the code press alt+F8 and select the macro and select the Step Into option. This will pull up the code. Anything that appears in green is a comment meant to help you understand.

Let me know if this works for you!

Thanks!

RVASQUEZ

Hi,

Thank you so much! I'll take a look & see if it works (I'm sure it will). I'll let you know.

JD

RV,

That macro worked wonders in the sample file, but when administered to the "real" file, things went haywire. I changed a couple cell #'s, but that's it. I can't figure it out.

I think the problem may lie within this string: (I'm not clear as to what the "6" refers to)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Here's how the 2nd sheet looks now:

ID# VENDOR MARKET PRICE
Company ABC, Inc. $1.00
1.000 Company ABC, Inc. $1.00
Company ABC, Inc. $1.00
1.000 Company ABC, Inc. $1.00
Company ABC, Inc. $1.00
1.000 Company ABC, Inc. $1.00

Compared to the correct layout you supplied earlier:
ID# VENDOR MARKET PRICE
1.000 Vendor, Inc. TX, Houston $1.20
1.000 Vendor, Inc. TX, Houston $1.20
1.000 Vendor, Inc. UT, Ogden $1.20
1.000 Vendor, Inc. AZ, Phoenix $1.20
1.000 Vendor, Inc. CA, San Diego $1.20
2.000 Vendor, Inc. TX, Houston $2.60
2.000 Vendor, Inc. UT, Ogden $2.30
2.000 Vendor, Inc. AZ, Phoenix $2.30
2.000 Vendor, Inc. CA, San Diego $2.30
3.000 Vendor, Inc. TX, Houston $10.25
3.000 Vendor, Inc. UT, Ogden $10.30
3.000 Vendor, Inc. AZ, Phoenix $9.98
3.000 Vendor, Inc. CA, San Diego $12.00

RV,
Could you do me a HUGE favor? I found out when I went in to edit the macro in Jackdaddy0711.xlsm that those edits save & I am unable to view your original masterpiece. Could you please include the code in the reply. I've nearly cracked it but need to refer back to the good version.

BTW, I figured out the "6" reference

Thanks, you have helped me enormously!
JD

Here is the file reattached.

Thanks!