formula to extract data

[zoharb]

Respected,
I need formula to extract data from one table, fetch the relevant data from other table and then merge it. Used Hlookup and Vlookup
but it didnt work as there is one ROW in table one and on basis of that two-three rows has to be fetched from TABLE2.
I need formula as it is to implemented in various files , so can make needed changes. (main for me is Arrays of data, so please give formulas as per that)
Zohar Batterywala

 

[alansidman]

With Power Query, Load each table to Power query. Apply the following Mcode to each and then merge the two tables as shown in the third Mcode.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Filtered Rows" = Table.SelectRows(Source, each ([Column1] <> null)),
    #"Uppercased Text" = Table.TransformColumns(#"Filtered Rows",{{"Column1", Text.Upper, type text}})
in
    #"Uppercased Text"

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    #"Filled Down" = Table.FillDown(Source,{"Column1"}),
    #"Uppercased Text" = Table.TransformColumns(#"Filled Down",{{"Column1", Text.Upper, type text}})
in
    #"Uppercased Text"

let
    Source = Table.NestedJoin(Table1, {"Column1"}, Table2, {"Column1"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(Source, "Table2", {"ID", "Qty", "Amount"}, {"ID", "Qty", "Amount"})
in
    #"Expanded Table2"

Book5
	A	B	C	D	E	F	G	H	I	J	K
2
3	Column1	code	location
4	ilias	As23	city
5
6	salim	S14	store-Ahh RSW
7
8	rakesh	RkD	Store-kpu YS
9
10
11	Column1	ID	Qty	Amount		Column1	code	location	ID	Qty	Amount
12	ilias	A-Dates.55150	2.53	1.2		ILIAS	As23	city	A-Dates.55150	2.53	1.2
13		A-A5001500gr	1.56	85		ILIAS	As23	city	A-A5001500gr	1.56	85
14	Salim	B-da1335	4.85	1.26		SALIM	S14	store-Ahh RSW	B-da1335	4.85	1.26
15		c-Alm	1.2	430		SALIM	S14	store-Ahh RSW	c-Alm	1.2	430
16	Rakesh	D-grap 1000gr	2.5	130		RAKESH	RkD	Store-kpu YS	D-grap 1000gr	2.5	130
17		E-cha	3.5	1.25		RAKESH	RkD	Store-kpu YS	E-cha	3.5	1.25
Sheet1

 

[zoharb]

Respected Alan Sidman,
Thank you VERY VERY MUCH for the solution
but I dont know how to use power Query as well as this sheet will be operated by client in future.
So PLEASE mention the exact procedure and how the client can use it. PLEASE
Zohar Batterywala

 

[alansidman]

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

It would also help if you gave us your sample data and expected result in a form that we can easily copy to test with. Check out XL2BB

 

[zoharb]

Respected Peter,
The output as generated by solution given by Alexsidman is correct as per my need
Table in Blue is input and Table in Green is output and al is on Same sheet of excel workbook.

BUT i have to use it at many places
AND also the end-user are not so excel savvy to use powerQuery, so i need and excel formula solution.
Please , it is getting data from from ~20+locations in (Ahmedabad, GUJARAT, INDIA) city in CSV format though email and has to be further computed at central office.
Many more application is possible.
PLEASE PLEASE help me.
Zohar Batterywala

 

[shaowu459]

Respected Peter,
The output as generated by solution given by Alexsidman is correct as per my need
Table in Blue is input and Table in Green is output and al is on Same sheet of excel workbook.View attachment 97668
BUT i have to use it at many places
AND also the end-user are not so excel savvy to use powerQuery, so i need and excel formula solution.
Please , it is getting data from from ~20+locations in (Ahmedabad, GUJARAT, INDIA) city in CSV format though email and has to be further computed at central office.
Many more application is possible.
PLEASE PLEASE help me.
Zohar Batterywala

Hi, please update your account details and let us know the Excel version you are using. For the data in post #6, here is one solution for your reference, using Microsoft 365 functions:

Book1.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1
2	Column1	code	location
3	ilias	As23	city
4
5	salim	S14	store-Ahh RSW
6
7	rakesh	RkD	Store-kpu YS
8
9
10	Column1	ID	Qty	Amount		Column1	code	location	ID	Qty	Amount
11	ilias	A-Dates.55150	2.53	1.2		ilias	As23	city	A-Dates.55150	2.53	1.2
12		A-A5001500gr	1.56	85		ilias	As23	city	A-A5001500gr	1.56	85
13	Salim	B-da1335	4.85	1.26		salim	S14	store-Ahh RSW	B-da1335	4.85	1.26
14		c-Alm	1.2	430		salim	S14	store-Ahh RSW	c-Alm	1.2	430
15	Rakesh	D-grap 1000gr	2.5	130		rakesh	RkD	Store-kpu YS	D-grap 1000gr	2.5	130
16		E-cha	3.5	1.25		rakesh	RkD	Store-kpu YS	E-cha	3.5	1.25
17
Sheet1

Cell Formulas
Range	Formula
F10:K16	F10	=HSTACK(MAP(A2:C8,LAMBDA(x,LOOKUP("々",OFFSET(x,,,-ROW(x))))),B10:D16)
Dynamic array formulas.

 

[zoharb]

Respected Shaowu459,
Thank you for giving me solution but clients end is not having office 365, office 2021 is available at out end , And also office 365 is not feasible for them as they are in intranet-no internet.
Zohar Batterywala
How to update office version that I use -I have tried to write in my profile at the place of my name, but as you are saying , it is not visible , so please guide me . I am using office 2021 for my daily use, but has office 2013 and office 2007 on same machine.

 

[zoharb]

done -updated the excel version. Please if possible give me formula for Excel 2021.

 

[shaowu459]

Respected Shaowu459,
Thank you for giving me solution but clients end is not having office 365, office 2021 is available at out end , And also office 365 is not feasible for them as they are in intranet-no internet.
Zohar Batterywala
How to update office version that I use -I have tried to write in my profile at the place of my name, but as you are saying , it is not visible , so please guide me . I am using office 2021 for my daily use, but has office 2013 and office 2007 on same machine.

Please refer to post #5, click the link and scroll down, you will find checkbox for excel versions, don't forget to save your changes.