Copying data from different worksheet

[Blu3St@r]

Hi there,

I have the following data stored in different worksheets as shown below.

The format of the data shown in sheet below is divided by '|' where it represents a cell divider.

Sheet1
-------
A | B | C
ClassA | ClassB | ClassC

Sheet2
-------
A | B
ClassA | Student01
ClassA | Student02
ClassB | Student01
ClassC | Student02
ClassC | Student03
ClassC | Student04

Sheet3 - Final Output
-------
A | B | C
ClassA | ClassB | ClassC
Student01 | Student01 | Student02
Student02 | | Student03
| | Student04

Sheet3 = check if there is a match between Sheet1 and Sheet2, if there is, then use Sheet1 as a header (ClassA, ClassB, ClassC) and paste the matched data under the respective header.

Appreciate if anyone could help or advice on what is the best way to achieve the output shown in Sheet3? I guess, there could be a way to do it via a formula or something else.

I'm totally new with excel formula/macros/programming, so would really appreciate if anybody could help me on this.

I've also attached a sample data in the attachment file for your references.

Thanks a lot in advance.


- Blue

[shg]

Welcome to the forum.

Please take a few minutes to read the forum rules, and then amend your thread title accordingly.

Thanks.

[Blu3St@r]

Hi,

Could anybody help me on this item?

Really appreciate for any of your help.

Thanks.

- Blue

[Blu3St@r]

Hi there,

I've tried using the vlookup formula, but I guess it's not working.

I used this formula:
=VLOOKUP(Sheet1!A1,Sheet2!A:B,2,FALSE)

Would appreciate if anybody could help me.

Thanks a lot.

[jaslake]

Hi Blu3St@r
I'm certain there's a more efficient way of doing this but...
The procedure in the attached seems to do what you require. Let me know if you have issues.

J

[Blu3St@r]

Hi Jaslake,

This is excellent. The formula works as expected.

I will let you know should there be any issues.

Thanks a lot. Greatly appreciated.


- Blue

[Blu3St@r]

Hi Jaslake,

I've tested the formula with more data and found that the formula is only catered for limited data as per my initial data above.

Is there a way to fix the formula so that it can handle more data?

Is it possible to make the formula more flexible?

For example: to process for more classes and more students?

Appreciate for any of your help. Thanks.


- Blue

[jaslake]

I'll look at this as soon as I can. J

[jaslake]

Hi Blue
I thought I uploaded this revised book early this afternoon. Must not have hit the submit button. The revised procedure allows you to enter as many students as you please; it also allows as many classes as you wish (as long as they are in Sheet1 header, they will be included). Try the revision and let me know of any issues.
>

[Blu3St@r]

Hi Jaslake,

Thanks a lot for your kind help.

I will test the new revision and let you know should there be any issues.

Greatly appreciated.

- Blue

[Blu3St@r]

Hi Jaslake,

Would like to seek for your advice if it's possible to produce the similar output in Sheet3?

Sample data is shown below as well as in the attachment file.

Sheet1
--------
ClassA | ClassB | ClassC
Group1 | Group2 | Group6
Group2 | Group5 | Group3
Group3 | Group1 | Group7

Sheet2
--------
Group1 | Playground
Group2 | Math
Group3 | Drawing
Group4 | Music
Group5 | History
Group6 | Science
Group7 | Lab

Sheet3
--------
ClassA | ClassB | ClassC
Group1 - Playground | Group2 - Math | Group6 - Science
Group2 - Math | Group5 - Histrory | Group3 - Drawing
Group3 - Drawing | Group1 - Playground | Group7 - Lab

Thank you for your kind help.


- Blue

[jaslake]

Hi Blue
You might try the attached. Certainly not the most elegant way to do this but it seems to work.

I've added a column of data on sheet2. I left it there so you could see what's happening. The data can be deleted when your satisfied as it will recreate itself in the procedure. Let me know if you have issues.

John