Copy row data

[gmoorthy]

I have a huge excel spreadsheet

Number description code date
1234 Test1 A 12/31/1998
1234 Test2 B 04/13/2020
7890 Test3 C 05/06/1999
4567 Test4 D 01/03/1999
4567 Test5 E 09/03/2020
4567 Test6 F 06/07/2020


What I want is that if the two numbers are same then description, code and date should be copied over to the first number

So it be
1234 Test1 A 12/31/1998 Test2 B 04/13/2020

 

[SkipVought]

Hi,

Check Jul 22, when I answered a very similar request.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[gmoorthy]

Skip I tried what you had suggested earlier. The issue is for example 4567 gets repeated 3 times so I have to keep dragging the formula across which is a manual process. Is there a way to sutomate this in such a away that it looks for the number of repeats takes the data and copies it acrosd

 

[Andrzejek]

If the "description, code and date should be copied over to the first number", so you expect your data to look like this?

[pre]
Number description code date
1234 Test1 A 12/31/1998 [blue]Test2 B 04/13/2020
1234 Test2 B 04/13/2020[/blue]
7890 Test3 C 05/06/1999
4567 Test4 D 01/03/1999 [blue]Test5 E 09/03/2020 Test6 F 06/07/2020
4567 Test5 E 09/03/2020
4567 Test6 F 06/07/2020[/blue]
[/pre]


---- Andy

There is a great need for a sarcasm font.

 

[gmoorthy]

Yes that is correct Andy

 

[Andrzejek]

Here is a little code that does that:

Option Explicit

Sub CopyStuff()
Dim intR As Integer
Dim intRToCopyTo As Integer
Dim intNoVal As Integer

intR = 2

Do While Range("A" & intR).Value <> ""
    If Range("A" & intR).Value = Range("A" & intR - 1).Value Then
        If intNoVal <> Range("A" & intR).Value Then
            intRToCopyTo = intR - 1
        End If
        
        intNoVal = Range("A" & intR).Value
                
        Range("B" & intR & ":D" & intR).Copy
        Range("A" & intRToCopyTo).End(xlToRight).Offset(0, 1).Select
        ActiveSheet.Paste
    End If
    
    intR = intR + 1
Loop

End Sub

I do realize that [tt].Select[/tt] could/should be changed to something more 'VBA proper', so feel free to comment/improve (Skip? )


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

@Andy,

1) On the topic of proper VBA, yes, I probably would have approached this problem differently, not only with respect to avoiding Select and Activate

2) Fundamentally, this kind of summary is ridiculous. It belongs in the spreadsheet solution category rather than VBA. I can vaguely remember doing such stuff in the 1990s using formulas but I can't remember why, except to think that I discovered much better ways to summarize data or analyze data to get the information I needed without such a ridiculous accross-the-page summarization. Anyhow, my IE buddy and I figured out how do this kind of goofy summarization for whatever reason, long before I learned to use VBA with success. A guy who is a (Programmer) ought to be able to figure out how to do this on a spreadsheet without help, IMNSHO.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

@Skip
2) I agree. I just took it as: "Here is a problem, and I need a VBA solution", and since I had a few minutes, there you go. As far as 'why do you need your data this way?' - I quit asking this question.


---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Well part of my purpose as a contributor to Excel-related questions is to also promote good, sound best practices, which this is not. I didn't want to take the time to interrogate the OP about purpose, so I reluctantly gave them a solution several months ago. Hey, drag the formula over (n*3)-1 columns. What's the big deal for a (Programmer)?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[gmoorthy]

I am getting a mismatch error

IntNoVal = Range (“A” & intR).Value

 

[Andrzejek]

What do you keep in column A?
Investigate the values of your variables:

...[blue]
Debug.Print "intR is " & intR
Debug.Print "In Column A row " & intR & " I have " & Range (“A” & intR).Value[/blue]
intNoVal = Range (“A” & intR).Value
...

What do you get - before the error - in Immediate Window from Debug statements?


---- Andy

There is a great need for a sarcasm font.

 

[gmoorthy]

Getting run time error 13 type mismatch

 

[SkipVought]

C'mon gmoorthy (Programmer). You oughta be able to figger this out.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[gmoorthy]

Int no Val = 0

Range a and intR = 45678

This is the first instance in the spreadsheet where numbers are repeated

 

[gmoorthy]

I have zero experience in VB

 

[SkipVought]

intR = 45678

Dim intR As Long

I'd declare ALL your numeric variables referring to Excel Row as Long as a matter of practice as Integer has a max value of 32767.

Now your Number field/column is another thing. Is Number really a number? Are you gonna do arithmetic on any value in the Number field, or is Number more like a Zip code, Part Number or Invoice Number? These are really Identifiers that yer not gonna add,msubtract, multiply or divide and as such, ought to be TEXT values.

FAQ68-6659

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[gmoorthy]

I am doing any calculation just have to copy the data

 

[gmoorthy]

Tried changing to text still the same issue

 

[SkipVought]

How do you have intR declared?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

If it is not a 'top secret', you may attach your file here so we can see your problem.

I did set up intR as Integer and it has a limit of 32767
So, it can never reach the value of intR = 45678, and if you pass its limit, the error would be 'an overflow', not a mismatch error.

---- Andy

There is a great need for a sarcasm font.