Need help with data manipulation please

[philmatth]

Hi,

Hopefully there's a solution for this. Where the pID is duplicated in column A and that pID has multiple images (lines 6-10) I need to move the data for the extra images for that pID to the pImage1, pImage2 fileds etc etc

So line 6 would look like this

pID pImage pImage1 pImage2 pImage3 pImage4

100.015UK WebImage2/100/100015_CO1.jpg WebImage2/100/100015_CO2.jpg WebImage2/100/100015_CO3.jpg WebImage2/100/100015_GS1.jpg WebImage2/100/100015_IN1.jpg


The whole sheet has around 8,000 lines with quite a few instances of this. Any help would be very much appreciated.

 

[MickG]

Try this:-

[COLOR="Navy"]Sub[/COLOR] MG01Jul54
[COLOR="Navy"]Dim[/COLOR] Rng     [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Dn      [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] k
[COLOR="Navy"]Dim[/COLOR] nRng    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Temp    [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]With[/COLOR] CreateObject("scripting.dictionary")
        .CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    [COLOR="Navy"]If[/COLOR] Not .Exists(Dn.Value) [COLOR="Navy"]Then[/COLOR]
        .Add Dn.Value, Dn
    [COLOR="Navy"]Else[/COLOR]
        [COLOR="Navy"]Set[/COLOR] .Item(Dn.Value) = Union(.Item(Dn.Value), Dn)
    [COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR]
 
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] k [COLOR="Navy"]In[/COLOR] .keys
    [COLOR="Navy"]If[/COLOR] .Item(k).Count > 1 [COLOR="Navy"]Then[/COLOR]
        [COLOR="Navy"]If[/COLOR] nRng [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Temp = .Item(k)(1).Offset(, 1)
            [COLOR="Navy"]Set[/COLOR] nRng = .Item(k).Offset(1).Resize(.Item(k).Count - 1)
        [COLOR="Navy"]Else[/COLOR]
            [COLOR="Navy"]Set[/COLOR] Temp = .Item(k)(1).Offset(, 1)
            [COLOR="Navy"]Set[/COLOR] nRng = Union(nRng, .Item(k).Offset(1).Resize(.Item(k).Count - 1))
        [COLOR="Navy"]End[/COLOR] If
            Temp.Resize(, .Item(k).Count) = .Item(k).Offset(, 1).Value
    [COLOR="Navy"]End[/COLOR] If
 [COLOR="Navy"]Next[/COLOR] k
        nRng.EntireRow.Delete
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[WinteE]

Or use this formula :

=IF(AND(COUNTIF($A:$A;$A2)>=2;ISNA(VLOOKUP($A2;$A$1:$A1;1;0)));HLOOKUP($B2;$B2:$B$100;2;0);"")

Copy this code to cell C1 and on from there.

Success

 

[philmatth]

Mick,

Brilliant - absolutely perfect! Many many thanks for your time. I can't tell you how much I appreciate your help on this.

Regards,
Phil

 

[philmatth]

Oops sorry Mick I spoke too soon

The images that are moved over are all the same

For example row 6 should have populated the following images in their respective columns
WebImage2/100/100015_CO1.jpg
WebImage2/100/100015_CO2.jpg
WebImage2/100/100015_CO3.jpg
WebImage2/100/100015_GS1.jpg
WebImage2/100/100015_IN1.jpg

But columns B to F has the same image in it - WebImage2/100/100015_CO1.jpg

and the same applies further down the sheet where there are multiple images per pID

WinteE - nothing happened when I pasted your code in c2
Regards,
Phil

 

[WinteE]

The =2 in the COUNTIF has to be 3 in column D, 4 in column E and so on. The same for the 2 in the HLOOKUP function.

Success

 

[MickG]

Slight oversight !!!
Change line shown in Red.

Else
            Set Temp = .Item(k)(1).Offset(, 1)
            Set nRng = Union(nRng, .Item(k).Offset(1).Resize(.Item(k).Count - 1))
        End If
            Temp.Resize(, .Item(k).Count) = Application.Transpose(.Item(k).Offset(, 1).Value)
    End If
 Next k

 

[philmatth]

Sorry but I.m pasting this code in c2 and nothing happens when I press enter

=IF(AND(COUNTIF($A:$A;$A2)>=2;ISNA(VLOOKUP($A2;$A$1:$A1;1;0)));HLOOKUP($B2;$B2:$B$100;2;0);"")

I gather I'm doing something wrong here? I understand your last statement.

 

[philmatth]

That did it - thanks Mick you're a star

 

[markmzz]

Sorry but I.m pasting this code in c2 and nothing happens when I press enter

=IF(AND(COUNTIF($A:$A;$A2)>=2;ISNA(VLOOKUP($A2;$A$1:$A1;1;0)));HLOOKUP($B2;$B2:$B$100;2;0);"")

I gather I'm doing something wrong here? I understand your last statement.

The formula of WinteE is very good. Try this:


<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2><COLGROUP><COL style="BACKGROUND-COLOR: #e0e0f0" width=25><COL><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP><THEAD><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH></TH><TH>A</TH><TH>B</TH><TH>C</TH><TH>D</TH><TH>E</TH><TH>F</TH><TH>G</TH><TH>H</TH><TH>I</TH><TH>J</TH></TR></THEAD><TBODY><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">1</TD><TD>pID</TD><TD>pImage</TD><TD>Image01</TD><TD>Image02</TD><TD>Image03</TD><TD>Image04</TD><TD>Image05</TD><TD>Image06</TD><TD>Image07</TD><TD>Image08</TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">2</TD><TD style="TEXT-ALIGN: right">12345</TD><TD>Image01</TD><TD>Image02</TD><TD>Image03</TD><TD>Image04</TD><TD>Image05</TD><TD>Image06</TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">3</TD><TD style="TEXT-ALIGN: right">12345</TD><TD>Image02</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">4</TD><TD style="TEXT-ALIGN: right">12345</TD><TD>Image03</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">5</TD><TD style="TEXT-ALIGN: right">12345</TD><TD>Image04</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">6</TD><TD style="TEXT-ALIGN: right">12345</TD><TD>Image05</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">7</TD><TD style="TEXT-ALIGN: right">12345</TD><TD>Image06</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">8</TD><TD style="TEXT-ALIGN: right">12346</TD><TD>Image07</TD><TD>Image08</TD><TD>Image09</TD><TD>Image10</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">9</TD><TD style="TEXT-ALIGN: right">12346</TD><TD>Image08</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">10</TD><TD style="TEXT-ALIGN: right">12346</TD><TD>Image09</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR><TD style="TEXT-ALIGN: center; COLOR: #161120">11</TD><TD style="TEXT-ALIGN: right">12346</TD><TD>Image10</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>


<TABLE style="BORDER-BOTTOM: black 2px solid; BORDER-LEFT: black 2px solid; PADDING-BOTTOM: 0.4em; BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 0.4em; PADDING-RIGHT: 0.4em; BORDER-COLLAPSE: collapse; BORDER-TOP: black 2px solid; BORDER-RIGHT: black 2px solid; PADDING-TOP: 0.4em" rules=all cellPadding=2 width="85%"><TBODY><TR><TD style="PADDING-BOTTOM: 6px; PADDING-LEFT: 6px; PADDING-RIGHT: 6px; PADDING-TOP: 6px">Worksheet Formulas<TABLE style="BORDER-BOTTOM: #a6aab6 1px solid; TEXT-ALIGN: center; BORDER-LEFT: #a6aab6 1px solid; BACKGROUND-COLOR: #ffffff; BORDER-COLLAPSE: collapse; BORDER-TOP: #a6aab6 1px solid; BORDER-RIGHT: #a6aab6 1px solid" rules=all cellPadding=2 width="100%"><THEAD><TR style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120"><TH width=10>Cell</TH><TH style="TEXT-ALIGN: left; PADDING-LEFT: 5px">Formula</TH></TR></THEAD><TBODY><TR><TH style="BACKGROUND-COLOR: #e0e0f0; COLOR: #161120" width=10>C2</TH><TD style="TEXT-ALIGN: left">=IF((COUNTIF($A:$A,$A2)>=COLUMNS($B$2:C2))*ISNA(VLOOKUP($A2,$A$1:$A1,1,0)),HLOOKUP($B2,$B2:$B$11,COLUMNS($B$2:C2),0),"")</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>


Markmzz