Find, Match and Populate Formula

Hi Experts,

I've attached a sample record that contains 2 worksheets (StudentRecord_Sample.xls).

Sheet1 contains student record information such as: Index, Group Name, Student ID, Activities
Sheet2 contains Student ID, Index

Sheet1 is a raw data. By only using the "given" student ID in Sheet2 (column A), how would it be possible to extract the "INDEX" information and populate its information as shown in colum B (yellow highlight)? Column A is the "given" input. Column B is the desired output.

Apprecite for your kind help.

Thank you.

- Blue

Hi Experts,

Anybody can help me please?

Thanks.


- Blue

Ideally is separate in different column, in one cell is possible but not efficient, manually involved, please see the file

Hi Azumi,

Thanks for your help.

I've tested and it's working as expected.

Yep, method 1 looks more efficient than 2.

However, I'm having some problem when I tested on a bigger raw data, let's say data with more than 1000 rows.

Will it be possible to make a flexible parameter to cater for this?

Thanks.


- Blue

Hi Experts,

Anybody else can help me please?

Thanks.


- Blue

Hi Experts,

Anybody?


- Blue

Originally Posted by Blu3St@r

Hi Azumi,

Thanks for your help.

I've tested and it's working as expected.

Yep, method 1 looks more efficient than 2.

However, I'm having some problem when I tested on a bigger raw data, let's say data with more than 1000 rows.

Will it be possible to make a flexible parameter to cater for this?

Thanks.

- Blue

Did adjust the ranges of formula for your actual workbook? I mean formula method 1?

Thanks

Hi Azumi,

Thanks for your response.

Yes, I've tried to adjust the values in the given formula, but somehow could not get it right.

The formula looks vey complicated, I may get lost somewhere

Could you help?

Thanks.


- Blue

Sure I'll help, just tell me which way

Hi Azumi,

Thanks for your response.

Could you help to update the formula for both methods so I could perform some testings?



- Blue

Ok Could you explain the situation of you actual table is? In what sheet, ranges, and where you put the formula?

I need the detail

Thanks

Hi Azumi,

Thanks for your response.

I've updated the excel files for both methods as per attached ("StudentRecord_Sample (Method1).xls" and "StudentRecord_Sample (Method2).xls").

Max number of raw data: 1000 rows.

Thanks.


- Blue

Hi Azumi,

Hope I've provide the required info.

Do let me know if you need anything.

Thanks.



- Blue

Sorry I've slow connection, last conversation, I workin on it

Im done with adjusted the ranges to 1000 rows, and follow this when you copy paste the formula:

1. Double click on formula first row, or press F2 button keyboard, so you can see formula inside cell
2. and then select it or blocked it wirh your mouse, from left to right formula, and copy that formula with your right click mouse or you can press CTRL-C)
3. and paste on desired workbook on desired cell, yes you must open you actual workbook first
4. Last step when you see the error, double click again or F2, when formula or cell contain formula open, press CTRL-SHIFT-ENTER button together (coz this array formula)

Thanks

Look at post #17, already updated

Hi Azumi,

Yep, I just saw your reply and had amended my response.

Could you help to check?

Thanks.


- Blue

Originally Posted by Blu3St@r

Hi Azumi,

Yep, I just saw your reply and had amended my response.

Could you help to check?

Thanks.


- Blue

Ok hold for a sec... maybe something missed

Would u check please, hope its right this time....

Hi Azumi,

Much appreciated for your response and help.

METHOD1 is working OK now, issue has been resolved.

I feel that it's much easier to manage METHOD2 but it doesn't matter since both are working OK.

Anyway, once again, thanks for your kind help.

Have a nice day.


- Blue

Hi icestationzbra,

Thanks for your help.

I've tried your method and found it quite complicated due to conditional formatting requirement. I couldn't get it working testing using my raw data.

Anyway, I've gotten a working solution from Azumi so I think I'll use that.

Thanks and have a nice day.


- Blue

Originally Posted by Blu3St@r

Hi Azumi,

Much appreciated for your response and help.

METHOD1 is working OK now, issue has been resolved.

I feel that it's much easier to manage METHOD2 but it doesn't matter since both are working OK.

Anyway, once again, thanks for your kind help.

Have a nice day.


- Blue

You're welcome, glad it works finally, thanks for feedback too....