Extract information from different sheets in a workbook

[Ruhina Afreen]

Hello everyone, and thank you in advance for taking out time to read my post. I am trying to extract information from different sheets which are available in specific columns and rows. I have more than 1000 sheets in a workbook which contains the information about the group and the users mapped to the specific group. The information of group is available in a column and different users mapped to it are in multiple rows of a sheet. similarly i have multiple sheets with information related to different groups.

I want to extract information of groups and users mapped in 1 single sheet as final output. I would need your expert advise on how to extract this information .I have attached a sample file with how the data would be available along with final output .

Looking for experts advise on how to achieve this scenario . Thank you again.


Afreen

[AliGW]

Administrative Note:

Welcome to the forum.

Members will tailor the solutions they offer to the version (NOT a release number like 2108) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.

[sandy666]

is that what you want?

you need to fix Groupb200 to Group200 probably

with Power Query

Group Members	UserID
Group100	test4
Group100	test5
Group100	test3
Group100	test1
Group100	test2
Group300	test14
Group300	test15
Group300	test13
Group300	test11
Group300	test12
Groupb200	test4
Groupb200	test5
Groupb200	test3
Groupb200	test1
Groupb200	test2

[Ruhina Afreen]

Hello ,

Thank you for your response.

yes you are right , the desired output must be exactly as mentioned by you. can you please elaborate on how to achieve this as i have around 1000 sheets with such information and i need all these to be consolidated in 1 single sheet. Looking for your support and Thank you in advance.

[AliGW]

Please update your profile as requested above. Do this NOW.

[Czeslaw]

Power Query

Please Login or Register  to view this content.

[sandy666]

if each range has the same structure select all and use Ctrl+T (Excel Table)
then you need to use Data tab - From Table for all tables
then in PQ editor, for each table there use

Please Login or Register  to view this content.

then use Append\

Please Login or Register  to view this content.

et voile see the result

[Ruhina Afreen]

Dear Experts,

First i would like to thankyou all for the responses.

There has been a change in the format of the output and there are additional columns added in each sheet with the information. can you please help me how to combine the data in all these sheets into a single sheet. In the New sample file i have added 2 sheets. I real time i have more than 1000 sheets to extract this information from. Your feedback will be appreciated. Thank you again for your response and feedback.

[sandy666]

Your source tables has nothing to do with your result table
but maybe this

Group	Display Name	SAM Account Name	Department
Group1	TEST Name1	ABCD	Finance
Group1	TEST Name2	TEST1	Procurement
Group1	TEST Name3	ZADCG	Finance
Group1	TEST Name4	OUIDHK	General Management
Group2	TEST Name5	JKLDJO	Credit
Group2	TEST Name6	JFHUIKD	IT
Group2	TEST Name7	NBHDFL	Finance
Group2	TEST Name8	JUIDLIK	IT

---

if you are happy hit Add Reputation at the bottom left side of the posts which have helped you