Excel 2007 : Finding and copying cells

**freehawk** · 12-10-2009, 06:07 PM

Hi. I guess I will get straight to the point. Can someone help me with the formula for finding the cells of special properties and copy them all into different sheet? For example:

A1 A2 A3 A4 A5 A6
1 10 20 30 40 101
2 10 20 30 40 102
3 10 20 30 40 103
1 11 19 30 40 101

I would like to know if there is a formula to find the rows by the sums of 5 numbers in columns A1:A5, and copy them. So, If I put 101, then whole row with the bold numbers will be selected, copied and then I can paste it in into different sheet. I guess its obvious that I want to try it with all the sums that will be in the worksheet, so its not just for one cell specific cell and number.

Thanks

**Huron** · 12-10-2009, 07:08 PM

I'm not sure if I understand your question. Are the numbers in columns A to E and totals in column F? If so, you could autofilter on column F and copy and paste the results.

**Ajmal Mirza** · 12-11-2009, 03:26 AM

Check the attached file... I think that it should solve your query.

You will have to manually copy and paste to new file... or we you can create a macro for doing that automatically if you wish so.

__________________________________________________________
A
A6 A1 A2 A3 A4 A5
=SUM(B2:F2) 1 10 20 30 40
=SUM(B3:F3) 2 10 20 30 40
=SUM(B4:F4) 3 10 20 30 40
=SUM(B5:F5) 1 11 19 30 40

A1 A2 A3 A4 A5
102 =VLOOKUP($B$12,$A$1:B5,2,0) =VLOOKUP($B$12,$A$1:C5,3,0) =VLOOKUP($B$12,$A$1:D5,4,0) =VLOOKUP($B$12,$A$1:E5,5,0) =VLOOKUP($B$12,$A$1:F5,6,0)

__________________

The result will look like this

A6 A1 A2 A3 A4 A5
101 1 10 20 30 40
102 2 10 20 30 40
103 3 10 20 30 40
101 1 11 19 30 40

A1 A2 A3 A4 A5
102 2 10 20 30 40

**Ajmal Mirza** · 12-11-2009, 03:36 AM

the attachment

**freehawk** · 12-11-2009, 01:21 PM

Sorry. I mean from A1 to F:1, I made mistake. I want to search the numbers in column F, through whole 65536 rows, and find sums individually, and automatically copy them WITH the numbers that add up to them. Here is an attachment showing with the number 122. It doesn't has to be copied in the rows below, because I have all 65536 rows filled, so in can be in columns between I and Z for example.

PS. Yeah Ajmal Mirza, something like that, but I doesn't just want one value. I have 65536, and put in to the side, on I column for example, and not below it. If the macro is not a problem to do for this type of question, I would really appreciate it.

Thanks