Compare columns

[Closed]
Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
Hello,
I need your help to resolve next problem: i have 5 columns (A, B,C,D,E) with these characteristics :
1. The length of all 5 columns are variable
2. The values from column A and E are identical, but in column A the values are multiplied.
3.Columns B,C,D contains the same values, but C&D may contains blank cells. Theoretically B, C,D are the same length, but considering that C and D may contain empty cells at the beginning or end, the length may appear to be different.
4. Values from column E are separated with 2 blank cell.
Now, i need a code in vb to do that: check every value of E and if is equal with A then copy , paste and transpose the value of B in range F, the value of C in F+1 and the value of D in F+2 I.e.
Initial situation:
A---------B-------C-------D--------E
blank--blank--blank--blank--blank
blank--blank--blank--blank--blank
blank--blank--blank--blank---val1
val1-----a--------a-----blank--blank
val2-----b-----blank-----b-----blank
val3-----c--------c-----blank----val2
val2-----d-------d-----blank--blank
val1-----d-----blank-----d----blank
val4-----a--------a-----blank---val3
val1-----c--------c-----blank--blank
val5-----b-----blank-----b----blank
val3-----e-----blank-----e------val4
val4-----f--------f-----blank--blank
val1-----g-------g-----blank--blank
val5-----h-----blank-----h------val5
.----------.-------.----------.----blank
.----------.-------.----------.----blank

After application of the code should look like this:
A---------B-------C-------D--------E-------F------G--------H
blank--blank--blank--blank--blank--blank--blank--blank
blank--blank--blank--blank--blank--blank--blank--blank
blank--blank--blank--blank---val1-----a------d--------g
val1-----a--------a-----blank--blank----a------g
val2-----b-----blank-----b-----blank----d-----blank
val3-----c--------c-----blank----val2-----b------d
val2-----d-------d-----blank--blank-----d-----blank
val1-----d-----blank-----d-----blank--blank--blank
val4-----a--------a-----blank---val3----c--------e
val1-----c--------c-----blank--blank----c-----blank
val5-----b-----blank-----b----blank----e-----blank
val3-----e-----blank-----e------val4----a--------f
val4-----f--------f------blank--blank----a-----blank
val1-----g-------g-----blank--blank-----f-----blank
val5-----h-----blank-----h------val5----b-------h
.----------.-------.----------.----blank--blank--blank
.----------.-------.----------.----blank----b-------h

Any help will be appreciated !
Thanks in advance!

2 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
Could you create a sample excel file with sample data showing how it is now and then on other sheet how you want it to see. You did try to show in your msg, but I am having a hard time making your your characteristics match with the sample. You would need to upload that sample file at some file sharing site and post back here the link to the file
Posts
1
Registration date
Wednesday October 12, 2011
Status
Member
Last seen
October 15, 2011

Hello rizvisa,
Thanks for prompt response . Unfortunately i don't know how to insert a sample excel in this message, so I'll repost my message in a different graphic form and will give detailed explanations:

Initial situation:
A       B     C     D      E 
                          val1 
val1     a    a
val2     b          b 
val3     c    c          val2 
val2     d    d 
val1     d          d
val4     a    a          val3 
val1     c    c
val5     b          b 
val3     e          e    val4 
val4     f    f 
val1     g    g 
val5     h          h    val5  
.           .      .       .        .
.           .      .       .        .
.           .      .       .        .
After application of the code should look like this: 
A         B     C     D     E         F      G      H    I
                            val1       a     d      c    g      
val1     a     a                       a     c      g
val2     b             b               d
val3     c     c            val2       b     d
val2     d     d                       d
val1     d             d               b
val4     a      a           val3       c      e 
val1     c      c                      c
val5     b             b               e 
val3     e             e    val4       a      f
val4     f      f                      a      f
val1     g      g 
val5     h             h    val5       b      h  
.        .       .     .           
.        .       .     .               b      h 
.        .       .     .     . 


I need a code in vb to do that: check every value of E and if is equal with A then copy , paste and transpose the value of B in range F, the value of C in F+1 and the value of D in F+2 I.e.

1. val1 from Range E2 appear four times in column A in ranges A4, A8, A10 and A14. In correspondence with A ranges in column B are values B4=a, B8=d, B10=c, B14=g ; in column C : C4=a, C8=blank, C10=c, C14=g; in column D : D4=blank, D8=d, D10=blank, D14=blank.
I want to copy, paste and transpose this values, eliminating blank values, as follows: values from column B starting with range F2 (F2 in correspondence to E2), so : F2=a, G2=d, H2=c, I2=g; values from column C starting with F3 so : F3=a, G3=c, H3=g; values from column D starting with F4 : F4=d
2.val2 from E5 appear two times in column A, in ranges A5 and A7. In correspondence we have: B5=b, B7=d; C5=blank, C7=d; D5=b, D7=blank. After copy, paste & transpose , will have: F5=b, G5=d; F6=d; F7=b.
This must be done for all values ??in column E.
I hope that this time i was more explicit.
Best regards,
Dan Paraianu
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
How many rows can be there for a particular match (val1, val2 etc) ?
If there are a lot of rows one way would be

1. filter on E column for val1
2. copy all visible rows and paste on a new sheet by selecting paste special transpose
3. keep filter on on E, and apply additional filter on c "where not blank"
4. copy visible rows and paste special by transpose
5. remove the filter from column c and apply same "not blank filter on column D
6. copy visible rows and paste special by transpose

repeat same for all other values

other way would be that you loop thru each row