Macro, VLOOK, or "IF" formula?

[Jackdaddy0711]

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

[rvasquez]

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!

[Jackdaddy0711]

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

[rvasquez]

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

[Jackdaddy0711]

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

[Jackdaddy0711]

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)

'cell in column C of the first empty row is equal the value in the cell whose
                'range is row 6 and the current cell in the loop's column
                .Range("C" & LR2).Value = Cells(6, c.Column).Value

Sub Verticaldatatosheet2()
Dim c As Range, LR As String, LR2 As String 'declare variables

Sheets(1).Select    'select the first worksheet in the workbook

LR = Sheets(1).Range("A408").End(xlUp).Row 'set LR equal to the last row in column A that contains a value

'loop through cells in the first worksheet in the workbook
'cells H15 through FU and row LR (defined above)
For Each c In Sheets(1).Range("H15:FU" & LR).Cells
    If c.Value <> 0 Then    'if the value in the current cell in the loop is not 0 then
        With Sheets(2)  'with the second worksheet in the workbook
            LR2 = .Range("409").End(xlUp).Row + 1 'set LR2 equal to the first empty row in column B
                
                'cell in column A of the first empty row is equal to the current cell in the loop's value in column C in sheet1
                .Range("A" & LR2).Value = Range("C" & c.Row).Value
                
                'cell in column B of the first empty row is equal to cell E3 of sheet1
                .Range("B" & LR2).Value = Range("E3").Value
                
                'cell in column C of the first empty row is equal the value in the cell whose
                'range is row 6 and the current cell in the loop's column
                .Range("C" & LR2).Value = Cells(6, c.Column).Value
                
                'cell in column D of the first empty row is equal to the current cell in the loop's value
                .Range("D" & LR2).Value = c.Value
        End With
    End If
Next c  'move to next cell in the loop

End Sub

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

[Jackdaddy0711]

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

[rvasquez]

Here is the file reattached.

Thanks!