Summarizing the data base

[MaxHa]

Hi,

Can someone please advise how to proceed?


I have a data base including 3 columns and 22000 rows. In the first column I manually add value=1 for which cells in the second column that is critical and leave the rest of the cells empty.


In another sheet, I need to summarize this data base for another purpose. So only the cells(in the second column) with value 1 is important for me.


If i use "If" formula combined with v lookup, something like +IF(MATRIX!A:A=1,V LOOKUP(MATRIX!B:B, MATRIX!B:E,1,0),) it will still give me the empty cells as well and I will have another file with 22000 rows while I only need the critical ones.


Thoughts?

	A000062 <tbody> </tbody>	Truck Chassis <tbody> </tbody> <tbody> </tbody>
1	A000065 <tbody> </tbody>	Truck Chassis <tbody> </tbody> <tbody> </tbody>
1	A000077 <tbody> </tbody>	Truck Chassis <tbody> </tbody> <tbody> </tbody>
1	A001840 <tbody> </tbody>	12 Stone Trolley <tbody> </tbody>
	A001843 <tbody> </tbody>	12 Stone Trolley <tbody> </tbody>

<tbody>
</tbody>

table above is just a sample, this DB has 22000 rows. So basically I need to only pick up cells in the second column which has value=1 in the first column.

 

[vcoolio]

Hello MaxHa,

Here is a VBA method if you are interested:-

Sub Transfer()
Application.ScreenUpdating = False
With ActiveSheet
    .AutoFilterMode = False
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .AutoFilter 1, 1
        On Error Resume Next
        .Offset(1, 2).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
    End With
    .AutoFilterMode = False
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet2.Select
End Sub

The code will filter Column A for any value of 1 and then transfer the relevant value from Column B to sheet2, Column A.

Place the code in a standard module and assign it to a button.

Test it in a copy of your work book first.

I hope that this helps.

Cheerio,
vcoolio.

 

[MaxHa]

Thanks vcoolio,

I tried this code and seems there is an error: .AutoFilter 1, 1

Hello MaxHa,

Here is a VBA method if you are interested:-

Sub Transfer()
Application.ScreenUpdating = False
With ActiveSheet
    .AutoFilterMode = False
    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        .AutoFilter 1, 1
        On Error Resume Next
        .Offset(1, 2).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
    End With
    .AutoFilterMode = False
End With
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet2.Select
End Sub

The code will filter Column A for any value of 1 and then transfer the relevant value from Column B to sheet2, Column A.

Place the code in a standard module and assign it to a button.

Test it in a copy of your work book first.

I hope that this helps.

Cheerio,
vcoolio.

 

[vcoolio]

Hello MaxHa,

Following is the link to my test work book. Click on the "RUN" button to see it work. You'll see that it works fine.

https://www.dropbox.com/s/qz00l3wnwnr0xkm/Sheet1 to sheet2 transfer,autofilter.xlsm?dl=0

If you are still receiving an error message in your work book, what does the message say?

Cheerio,
vcoolio.

 

[MaxHa]

It works perfectly now. Thanks very much for your help vcoolio.

 

[vcoolio]

You're welcome Max. Glad that I was able to help.

Cheerio,
vcoolio.