Compare columns
Closed
danparaianu
-
Oct 14, 2011 at 05:04 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Oct 18, 2011 at 06:09 PM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Oct 18, 2011 at 06:09 PM
Related:
- Compare columns
- Beyond compare - Download - File management
- Display two columns in data validation list but return only one - Guide
- Tweetdeck larger columns - Guide
- Excel compare two sheets - Guide
- How to delete rows and columns in word - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Oct 15, 2011 at 07:38 AM
Oct 15, 2011 at 07:38 AM
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
danparaianu
Posts
1
Registration date
Wednesday October 12, 2011
Status
Member
Last seen
October 15, 2011
Oct 15, 2011 at 02:18 PM
Oct 15, 2011 at 02:18 PM
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:
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
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
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Oct 18, 2011 at 06:09 PM
Oct 18, 2011 at 06:09 PM
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
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
