Related Posts
Additional Posts in Excel Genius
Anyone know what these badges are?
New to Fishbowl?
Download the Fishbowl app to
unlock all discussions on Fishbowl.
unlock all discussions on Fishbowl.
That was just a preview…
Sign Up to see all discussions
Sign Up to see all discussions
- Discover what it’s like to work at companies from real professionals
- Get candid advice from people in your field in a safe space
- Chat and network with other professionals in your field
Sign up in seconds to unlock all discussions on Fishbowl.
"" is an unusual email domain. If correct, please proceed to the next step
Messaging rates may apply
This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
New to Fishbowl?
Download the Fishbowl app to unlock all discussions on Fishbowl.
Share
Embed this post
Copy and paste embed code on your site
Preview
Download the
Fishbowl app
Scan your QR code to download
Fishbowl app on your mobile
Sign up for free to view this conversation on Fishbowl
Sign up for free to continue using Fishbowl
Messaging rates may apply
I presume you tried vlookup?
Is it necessary to have the same names in three different columns?
Not at all. My comment here wasn’t even a critique, it was offering a possible explanation for having the names in three columns.
Also, if you scroll a little further down the comments, you will see the solution that I provided earlier.
Step 1: In a different Sheet (Sheet2), Make 3 columns - Name, Score1, Score 2
Step 2: Copy all names from Sheet1 in column A (Name) of Sheet 2. Once done, Under that copy all names in Column F (this is to ensure that you have all names).
Step 3: Select Column “Name” in sheet 2. From data tab - select “Remove Duplicates”
Step 4: in First row for column Score 1 in Sheet 2 (cell B2), write formula =vlookup(A2, Sheet1!:A:B,2,0) and press enter. You should have score 1 of the first person in your sheet 2.
Step 5: in the first Row for column Score2 in sheet 2 (cell C2), write formula =vlookup(A2, Sheet1!:F:G,2,0) and press enter. You should have score 2 of the same person in Cell C2.
Step6: Once verified, drag both formulas to complete the list.
Job Done.✔️
Thank you Sir/Mam.
Copy names into cell h then do two different vlookups on name?
There's probably a simpler solution...but this one is pretty neat and versatile. Better if your names/scores are stored in formatted tables so you could refer to the columns by name.
Thank you Sir/Mam.
If you are trying to combine the the information in the cells, use CONCATENATE.
It sounds like they are trying to build a table with three columns…name, score1, and score2
Why not just delete the columns between the 2 score columns?
😂
Literally copy your post and paste it into chatgpt and I guarantee you will get your answer.
Is the chat GPT 4 or the free version? Here is the response from 4:
To create a new list of names and scores in Columns H, I, and J by combining data from Columns A, B, F, and G in Excel, you can use a combination of Excel functions. Here's a step-by-step approach:
1. **Unique List of Names**: First, you need to create a unique list of names from Columns A and F. You can do this using a formula that combines the `UNIQUE` and `FILTER` functions. Place this formula in Column H, starting from H2:
```excel
=UNIQUE(FILTER(A2:A100, A2:A100<>"") & FILTER(F2:F100, F2:F100<>""))
```
This formula assumes your data goes down to row 100. Adjust the range as per your data size.
2. **Lookup Scores from Column A/B**: In Column I, starting from I2, you will retrieve the scores from Column B that correspond to the names in Column H. Use the `XLOOKUP` function:
```excel
=IFERROR(XLOOKUP(H2, A2:A100, B2:B100, ""), "")
```
This formula will return the score from Column B if the name in H2 is found in Column A; otherwise, it returns a blank.
3. **Lookup Scores from Column F/G**: In Column J, starting from J2, you will retrieve the scores from Column G that correspond to the names in Column H. Use a similar `XLOOKUP` function:
```excel
=IFERROR(XLOOKUP(H2, F2:F100, G2:G100, ""), "")
```
This approach will create a list of unique names in Column H with their corresponding scores from Columns B and G in Columns I and J, respectively. If a name is only in Column A and not in Column F (or vice versa), the corresponding score column will show a blank for that name. Remember to adjust the range in the formulas according to the actual size of your data.
Sometimes KISS is the best solution. If these names NEED to be in there that many times, a pivot table may be the easiest solution.