rearrange 3 columns to match a list

[John Caines]

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

no	url (COL B)	new (COL C)	server (COL D)	php (COL E)
1​	Want your own website? | 123 Reg	24S | Luxury Fashion: Women's and Men's designer clothes, bags & shoes	Apache	N/A
2​	http://2diggers.co.uk/	http://www.2catanddod.com/	Apache	PHP/7.0.33
3​	http:/star-classics.co/	http:/star-classics.co/	Apache	N/A
4​	http://www.2catanddod.com/	Want your own website? | 123 Reg	Apache	N/A
5​	http://4256constructionltd.com/	http://2diggers.co.uk/	Apache	N/A
6​	http:/star-classics.co/	http://4256constructionltd.com/	Apache	PHP/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

 

[John Caines]

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]

Is this what you are looking for as a result?

Book2
	A	B	C	D
1	Column2	Table2.Column3	Table2.Column4	Table2.Column5
2	Want your own website? | 123 Reg	Want your own website? | 123 Reg	Apache	N/A
3	http://2diggers.co.uk/	http://2diggers.co.uk/	Apache	N/A
4	http://www.2catanddod.com/	http://www.2catanddod.com/	Apache	PHP/7.0.33
5	http:/star-classics.co/	http:/star-classics.co/	Apache	N/A
6	http:/star-classics.co/	http:/star-classics.co/	Apache	N/A
7	http://4256constructionltd.com/	http://4256constructionltd.com/	Apache	PHP/5.6.40
Sheet3

 

[John Caines]

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]

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]

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]

Thanks for updating your profile.

Would something like this suffice?

20 10 10.xlsm
	A	B	C	D	E	F	G	H	I
1
2	1	Want your own website? | 123 Reg	24S | Luxury Fashion: Women's and Men's designer clothes, bags & shoes	Apache	N/A		Want your own website? | 123 Reg	Apache	N/A
3	2	http://2diggers.co.uk/	http://www.2catanddod.com/	Apache	PHP/7.0.33		http://2diggers.co.uk/	Apache	N/A
4	3	http:/star-classics.co/	http:/star-classics.co/	Apache	N/A		http:/star-classics.co/	Apache	N/A
5	4	http://www.2catanddod.com/	Want your own website? | 123 Reg	Apache	N/A		http://www.2catanddod.com/	Apache	PHP/7.0.33
6	5	http://4256constructionltd.com/	http://2diggers.co.uk/	Apache	N/A		http://4256constructionltd.com/	Apache	PHP/5.6.40
7	6	http:/star-classics.co/	http://4256constructionltd.com/	Apache	PHP/5.6.40		http:/star-classics.co/	Apache	N/A
John Caines

Cell Formulas
Range	Formula
G2:G7	G2	=B2
H2:I7	H2	=INDEX(D:D,AGGREGATE(15,6,ROW(D$2:D$7)/($C$2:$C$7=$G2),1))

 

[John Caines]

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

=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]

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]

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

What is Power Query?

Power Query description

www.poweredsolutions.co

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