Previous Topic Next Topic

Fill data according to other sheet's data

Solved/Closed
Migas.pt Posts 4 Registration date Wednesday March 12, 2014 Status Member Last seen March 20, 2014 - Mar 12, 2014 at 07:52 AM
Migas.pt Posts 4 Registration date Wednesday March 12, 2014 Status Member Last seen March 20, 2014 - Mar 20, 2014 at 03:18 PM
I have a sheet1 like this:

r1 text1 text 1
r2 text1 textx 2
r2 text1 textx 0
r3 text2 texty 1
r4 text2 textz 1
r5 text2 textx 3
r6 text2 textx 0
r7 text2 textx 0
r8 text3 textv 1

On this 4th column i created a formula to count the number that the text in column 3 occurs if the text in colum 2 is unique, and give 0 whenever it was already accounted for: =IF(F3=F2;0;IF(F3=F4;COUNT.IF.S(F1:F297;"*" & F3;E1:E297;"*" & E3);1)) -> original formula i used, with original colums+rows.

What I pretend is that on sheet2 I use the data from row 4 on sheet1 but don't use it if there is a 0:

r1 text1 text 1
r2 text1 textx 2
r3 text2 texty 1
r4 text2 textz 1
r5 text2 textx 3
r6 text3 textv 1

Is this possible to do on Excel?

Thanks in advance!
Related:

3 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Mar 17, 2014 at 12:17 PM
Hi Migas.pt,

The following code will loop through column D of Sheet1. When the value found is not equal to zero then the entire row is pasted to Sheet2. So change the column D references and try it out: 
Sub RunMe()
Dim lRow As Integer

Sheets("Sheet1").Activate
lRow = Range("D1").End(xlDown).Row

For Each cell In Range("D1:D" & lRow)
    If cell.Value <> 0 Then cell.EntireRow.Copy Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell
End Sub

Best regards,
Trowa
Migas.pt Posts 4 Registration date Wednesday March 12, 2014 Status Member Last seen March 20, 2014
Mar 18, 2014 at 06:08 AM
Hey TrowaD!

Thanks for the answer but when I click "Run" it gives me this error: Overflow

Here is the code adapted to my situation:

"Sub RunMe()
Dim lRow As Integer

Sheets("Nº Docentes").Activate
lRow = Range("K3").End(xlDown).Row

For Each cell In Range("K3:K" & lRow)
If cell.Value <> 0 Then cell.EntireRow.Copy Sheets("Folha1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell
End Sub"

My values are on "Nº Docentes" sheet, from K3:K299"

My sheet2's name is "Folha1"
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Mar 18, 2014 at 11:56 AM
Hi Migas.pt,

Normally Overflow means that there are more rows, then we reserved.

By Dimming lRow as integer, we reserved 32.767 rows.

You can try 2 things:
1) Since you have a fixed range, we can put that in the code: 
Sub RunMe()
Sheets("Nº Docentes").Activate

For Each cell In Range("K3:K299")
    If cell.Value <> 0 Then cell.EntireRow.Copy Sheets("Folha1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Next cell
End Sub

2) Or we can reserve more rows by replacing Integer (32.767 rows) by Long (2.147.483.647 rows).
Change the code line "Dim lRow As Integer" by "Dim lRow As Long"

If both methods fail, then consider uploading your file.

Best regards,
Trowa
Answer 2 / 3
Migas.pt Posts 4 Registration date Wednesday March 12, 2014 Status Member Last seen March 20, 2014
Mar 19, 2014 at 06:19 AM
HI Trowa,

It seems that this code is reproducing all cells that have values in my "Nº Docentes" sheet to my "Folha1" sheet, not merely copying the data from row K.

I tested both 1) and 2) but i don't think the problem is the number of rows, what do you think?

Yes, I need to upload it.

Here it is:

https://drive.google.com/file/d/0BxeeJhC4K3C9S1JwRjFJclhMV0E/edit?usp=sharing

Since I've worked with my Excel that is in Portuguese, will that not do any harm when you open it?
Answer 3 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Mar 20, 2014 at 12:08 PM
Hi Migas.pt,

I guess there is some confusion as to what you want to achieve.

In you original post you had data like:
r2 text1 textx 2
r2 text1 textx 0

And after running the code you wanted:
r2 text1 textx 2

In your file you have data like (from row 5 and 6):
3 1 1 Administração Pública Introdução às Ciências Políticas e Sociais 2
0 1 1 Administração Pública Introdução às Ciências Políticas e Sociais 0

So after running the code, data would look like:
3 1 1 Administração Pública Introdução às Ciências Políticas e Sociais 2

Now I understand you only want to keep the data in the last column (k).

For that use the following code: 
Sub RunMe()
Application.ScreenUpdating = False

Sheets("Nº Docentes").Activate

For Each cell In Range("K3:K299")
    If cell.Value <> 0 Then
        cell.Copy
        Sheets("Folha1").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
    End If
Next cell

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

NOTE: Overflow happened because you ran the code from the sheet, where it is supposed to be run from a module. (Top menu > Insert > Module)

Mini note: Language doesn't matter, only the data entered by you will remain in Portuguese.

How does that work for you?

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Migas.pt Posts 4 Registration date Wednesday March 12, 2014 Status Member Last seen March 20, 2014
Mar 20, 2014 at 03:18 PM
Thanks man, that's exactly what I wanted. Now i just need to adjust the code to my needs :)

Thank you very much Sir!

Have a good life ;)

Similar discussions

Unlock safe folder without losing any data
Mintu -
HelpiOS -

1 response
How To Fix Mint Mobile Data Not Working?
sachin327 -
joshitha -

2 responses
AMD data change update new data to DMI
Luis -
dwebb -

1 response