VBA to match and sort data in two columns, then copy to another two colums

[P1w2]

Hi All, looking for some direction on this one, thanks in advance.

I have copied data from an external website and cleaned it up. I need to sort the data in a couple of columns (H and J) and copy it to columns A and B maintaining the integrity of the row so I can use VLOOKUP from another sheet.

The data in columns (H and J) is mixed but I want to sort it so if the content partially matches a specific text it gets copied to Colum A but if it doesn’t match then it is to be copied into column B. Sometimes columns H and J will both contain a partial match to the specific text, in those instances I want to copy the data in column H to column A and from column J to column B. This is the result I'm trying to achieve

Text to partially match within a cell = “Ro Roc”

EG.PNG

[Kaper]

In the example (screenshot ) you used only match, not partial match. If it's the case for column B (cell B2 and copy down) you could use a formula:

Please Login or Register  to view this content.

If you do want to look for partial match, would you please attach a SMALL sample Excel workbook (10-20 rows of data is usually enough)?

Please don't attach a picture of one (no-one will want to re-type all your stuff before starting).

1. Make sure that your sample data are truly REPRESENTATIVE of your real data. The use of unrepresentative data is very frustrating and can lead to long delays in reaching a solution.

2. Make sure that your desired solution is also shown (mock up the results manually).

3. Make sure that all confidential information is removed first!!

Unfortunately the attachment icon doesn't work at the moment. So, to attach an Excel file you have to do the following: Just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

[P1w2]

Hello Kaper, thank you for you fast response and helping me to look at my problem.

Apologies if I have over simplified my explanation below.



The attached workbook contains the actual data that I have (by VBA) copied data from an external website, cleaned up and removed rubbish, called “Data” sheet. The “CF” sheet will be the final output.

I am trying to get the “data” sheet into a state that VLOOKUP on CF sheet will fill in the blanks.

“Rowville Rockets” is a junior basketball club. “B9 Red” is a team my result is to know when, where and I will be happy if we get all but the duplicate Rowville Rockets teams to work.

In my “Data” Sheet, columns C:K contains the imported data. The populated rows in columns A and B is examples of mock results.

Basically all “Rowville Rockets” teams in column A, opponent or “Bye” in Column B. We may have to error on 2 “Rowville Rockets” teams.

I am wondering If I have overlooked a simple method.

Thank you for your help.
P1w2

[Kaper]

Have you tried to use the formula I proposed in post #2 to your data. Namely E6:

Please Login or Register  to view this content.

(it's the simplified version, as used in the attachment - without outmost Iferror
of course it can be added:

Please Login or Register  to view this content.

May be (I've not analyzed the data) all ranges shall be shortened to ...$81 , to cover only Oct 14th results and similar formula shall be written for Oct 15th (so ranges starting not from ...$2:... but with ...$82:....

[P1w2]

Hello Kaper,
Thank you for helping me to look at my problem.

Your formula works very well, Thank you. (I did try and use it another way but no success) I am now down to the final parts of the total solution. 😊

I have attached the latest “test” workbook. I am using VBA modules to clean up the data ready for VLOOKUP’s to work.

The next step is to populate Columns “C” and “D” on the “Fixture” sheet matching the team in Column “E” with Columns “D” and “E” on the “DataSet” sheet.
I think there may be two ways but not sure of the best or how to do it:

1. Sort Columns ”A” and “B” that match “Rowville Rockets” into one column. Or
2. Use a similar formula to the one you have already provided. (INDEX,MATCH)

What do you think?

Thanks you for your help.

[P1w2]

HI, thanks for your help Kaper, I have been able to finish my sheet. Marking this solved.

[Kaper]

Glad to hear so And thanks for marking solved