rearrange 3 columns to match a list

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
Hello all.
I really am struggling here.
I have a long list of URLs In Column B2. Column A is just numbered, From A2 (1 to 984)

And I have data in columns, C2, D2 & E2
Column C2 also has a list of URLs.

Here's what I'm trying to do.

I really need a formula to look at the URLs in COL B, (And this column of URLs does contain duplicates which I must keep), and see what URLs match with this from COL C.
When there is a match, put all 3 cells from columns CD & E onto the same ROW as the matching URL

Does this make sense?
Here's a screenshot as an example
nourl (COL B)new (COL C)server (COL D)php (COL E)
1​
Want your own website? | 123 Reg24S | Luxury Fashion: Women's and Men's designer clothes, bags & shoesApacheN/A
2​
http://2diggers.co.uk/http://www.2catanddod.com/ApachePHP/7.0.33
3​
http:/star-classics.co/http:/star-classics.co/ApacheN/A
4​
http://www.2catanddod.com/Want your own website? | 123 RegApacheN/A
5​
http://4256constructionltd.com/http://2diggers.co.uk/ApacheN/A
6​
http:/star-classics.co/http://4256constructionltd.com/ApachePHP/5.6.40

So, it's;
1. Make Col C, D, & E all rearrange so COL C URLs along with Col D & E) rearrange to the same order as Column B URLs.

I really hope this makes sense.

Hope someone can help me out here.
I'm trying to find this on the forum, as I'm sure this must have been done many times, but I just can't see it!

Hope someone can save my day!

Many thanks in advance

Best regards
John C
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
PS, Just to add, Column B has 984 URLs. Where as COLUMN D, E & F only has 765 rows of data.
But these blank cells in COL C,D & E would need to be populated, as Col B has Duplicates in, hense why it is longer.

Hope this makes sense.
Just thought I needed to clarify this

Many thanks in advance
Best regards
John C
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,146
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Is this what you are looking for as a result?

Book2
ABCD
1Column2Table2.Column3Table2.Column4Table2.Column5
2Want your own website? | 123 RegWant your own website? | 123 RegApacheN/A
3http://2diggers.co.uk/http://2diggers.co.uk/ApacheN/A
4http://www.2catanddod.com/http://www.2catanddod.com/ApachePHP/7.0.33
5http:/star-classics.co/http:/star-classics.co/ApacheN/A
6http:/star-classics.co/http:/star-classics.co/ApacheN/A
7http://4256constructionltd.com/http://4256constructionltd.com/ApachePHP/5.6.40
Sheet3
 

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
Hi Alan,
Yes, exactly, looks correct what you show above in your table
Basically, whatever URL I have in Column B,, if it finds the same URL anywhere in Column C, return all 3 columns (C,D,E) onto the same row number. That's it!

BTW, if it is easier, there's no problem writing a formula in a empty cell and returning all in new empty columns, ie E.F.G if it's simpler to write.

But as your photo above shows Alan, that is looking correct.

So the question is;
How to get this result! :)
Cheers Alan
John C
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,368
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

So the question is;
How to get this result!
I suggest that you update your Account details (click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Are you looking for a solution by vba, Power Query, Formula?
 

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
Hi Peter_Sss!
Sorry about that. I have now updated my excel version (2019) (Windows), 10 64bit

As to Power Query / Formula, I really don't know! LOL, To be honest Peter, I don't know exactly what power Query is / does, so seeing as you mentioned it, I'm now on youtube watching,
'
'Excel Power Query Course: Power Query Tutorial for Beginners' :)

To be honest Peter, how I 1st explained my request probably wasn't the best.
I'm very happy having say a formula copied down in empty columns if needs be, and results returned in empty columns if needs be, rather than the 3 columns of data rearrange themselves.

The main point is, whatever URL is in COL B,,,, the 3 columns of data need to be populated on the same rows as Col B,, (So long as there is a matching URL in Col C).

I hope that all makes sense?

Thanks Peter.
All the best
John C
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,368
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for updating your profile. (y)

Would something like this suffice?

20 10 10.xlsm
ABCDEFGHI
1
21Want your own website? | 123 Reg24S | Luxury Fashion: Women's and Men's designer clothes, bags & shoesApacheN/AWant your own website? | 123 RegApacheN/A
32http://2diggers.co.uk/http://www.2catanddod.com/ApachePHP/7.0.33http://2diggers.co.uk/ApacheN/A
43http:/star-classics.co/http:/star-classics.co/ApacheN/Ahttp:/star-classics.co/ApacheN/A
54http://www.2catanddod.com/Want your own website? | 123 RegApacheN/Ahttp://www.2catanddod.com/ApachePHP/7.0.33
65http://4256constructionltd.com/http://2diggers.co.uk/ApacheN/Ahttp://4256constructionltd.com/ApachePHP/5.6.40
76http:/star-classics.co/http://4256constructionltd.com/ApachePHP/5.6.40http:/star-classics.co/ApacheN/A
John Caines
Cell Formulas
RangeFormula
G2:G7G2=B2
H2:I7H2=INDEX(D:D,AGGREGATE(15,6,ROW(D$2:D$7)/($C$2:$C$7=$G2),1))
 

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
Hi Peter!
Many thanks for getting back to me on this!
Very much appreciated.

I was actually in the process of replying saying your formula doesn't work!
I made a nice big reply explaining the problem, then I saw an error on my part!

I then thought I needed 2 different formulas to get it to work (1 for Col H & 1 for Column I (even though I can now see you clearly stated the formula for H2:I7!

Again, I wrote a nice long reply no 2 explaining this,,, then I saw my error again!

So in summary Peter.
Your formual works just fine! It's me that needs fixing! LOL ;)

So, for cell H2:I985 I have
Code:
=INDEX(D:D,AGGREGATE(15,6,ROW(D$2:D$985)/($C$2:$C985=$G2),1))
& all is now good I think. I've been checking random cells against the main sheet and all is returned correctly; I'm pretty sure.
It's even now highlighted errors I didn't know of as if it doesn't get a match for the server type or PHP it says; #NUM!
This is brilliant as I can double check these few manually.

This is amazing!
This would have taken me hrs to do manually!

Really appreciated Peter as this is the last piece in a puzzle for a sheet I have to make.
I'm really really grateful for this.
I'll have to save this sheet as I'm sure I'll be needing this again somewhere down the line; it is extremely useful for me.

I'll carry on watching my youtube video (from someone called Simon Sez IT), it is an interesting Power Query beginners course!
(even though you didn't need to go this route).

Have a great day Peter.
Again many thanks


PS, if I have made any mistakes in the formula I mentioned, please let me know,
But I'm pretty sure all is ok here now,, so grateful.
Cheers Peter!
Yours sincerely
A very happy
John Caines
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,146
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
I used Power Query to achieve the results I presented.

I brought your original range into the PQ editor.
I then replicated the table in the PQ editor.
In the first table, I deleted all columns except the first.
I then merged (left inner join) the two tables based upon the URL as a common field.
Column B from first table and Column C from the second table

If you wish to learn more about Power Query, I urge you to pick up the book "M is for (Data) Monkey" by Ken Puls and Miguel Escobar available in the MrE bookstore.
 

John Caines

Well-known Member
Joined
Aug 28, 2006
Messages
1,139
Office Version
  1. 2019
Platform
  1. Windows
Hi Alan,
Many thanks for your reply.

This whole Power Query is new to me to be honest, so I'll tread slowly / carefully ,,, looks really powerful and very useful!
I saw your link in your footer

I'll read into this a bit more.
Many thanks for taking the time to reply.
All help here is greatly appreciated.

I've read what you've said, but it's still a little over my head to be honest, I'd need to see it, like a screencam example of what you're saying to fully grasp that at the moment.

I'll watch this video I'm on, as I'm sure that will get me to understand a lot better / fully what you've done etc

Cheers Alan
And thanks again
Best regards
John C
 

Watch MrExcel Video

Forum statistics

Threads
1,127,105
Messages
5,622,764
Members
415,926
Latest member
jerrynababa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top