VBA Browse a File and Get Certain Data

[unknownymous]

Hi Team,

So I usually work on a particular task where i need to collate specific data. I need to produce a sheet where it has 5 columns in it namely ID, Name, Note, Source and KEY columns. I was thinking of using Getopen file were it will openthe source sheet which is normally in xls or xlsb format.

See below Source Data Format:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Holdings[/TD]
[TD]Y/N[/TD]
[TD]Note[/TD]
[TD]Source[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Ann[/TD]
[TD]23000[/TD]
[TD]N[/TD]
[TD]Follow-up[/TD]
[TD]Online[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Ben[/TD]
[TD]10000[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Paper[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]Sue[/TD]
[TD]15000[/TD]
[TD]N[/TD]
[TD]Pending[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]Chris[/TD]
[TD]21000[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Fax[/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD]Amy[/TD]
[TD]9000[/TD]
[TD]Y[/TD]
[TD]Done[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]006[/TD]
[TD]Shawn[/TD]
[TD]5336[/TD]
[TD]N[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]007[/TD]
[TD]Sel[/TD]
[TD]1030[/TD]
[TD]Y[/TD]
[TD][/TD]
[TD]Online[/TD]
[/TR]
</tbody>[/TABLE]


Expected Output:
Get the necessary columns and as long as the excel has value on both Note or Source (vice versa), the KEY column will indicate as YES

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Name[/TD]
[TD]Note[/TD]
[TD]Source[/TD]
[TD]KEY[/TD]
[/TR]
[TR]
[TD]001[/TD]
[TD]Ann[/TD]
[TD]Follow-up[/TD]
[TD]Online[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]002[/TD]
[TD]Ben[/TD]
[TD][/TD]
[TD]Paper[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]003[/TD]
[TD]Sue[/TD]
[TD]Pending[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]004[/TD]
[TD]Chris[/TD]
[TD][/TD]
[TD]Fax[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]005[/TD]
[TD]Amy[/TD]
[TD]Done[/TD]
[TD][/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD]007[/TD]
[TD]Sel[/TD]
[TD][/TD]
[TD]Online[/TD]
[TD]Y[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Any help will be much appreciated. Thank you!