formula to extract data

zoharb

Board Regular
Joined
Nov 24, 2011
Messages
57
Office Version
  1. 2021
  2. 2013
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
 

Attachments

  • excel problem-18aug23MrExCel.jpg
    excel problem-18aug23MrExCel.jpg
    101.4 KB · Views: 15

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.
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.

Power Query:
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"


Power Query:
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"


Power Query:
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
ABCDEFGHIJK
2
3Column1codelocation
4iliasAs23city
5
6salimS14store-Ahh RSW
7
8rakeshRkDStore-kpu YS
9
10
11Column1IDQtyAmountColumn1codelocationIDQtyAmount
12iliasA-Dates.551502.531.2ILIASAs23cityA-Dates.551502.531.2
13A-A5001500gr1.5685ILIASAs23cityA-A5001500gr1.5685
14SalimB-da13354.851.26SALIMS14store-Ahh RSWB-da13354.851.26
15c-Alm1.2430SALIMS14store-Ahh RSWc-Alm1.2430
16RakeshD-grap 1000gr2.5130RAKESHRkDStore-kpu YSD-grap 1000gr2.5130
17E-cha3.51.25RAKESHRkDStore-kpu YSE-cha3.51.25
Sheet1
 
Upvote 1
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
 
Upvote 0
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.
 
Upvote 1
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
 
Upvote 0
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.
excel problem-23aug23MrExCel.jpg

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
 
Upvote 0
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
ABCDEFGHIJK
1
2Column1codelocation
3iliasAs23city
4
5salimS14store-Ahh RSW
6
7rakeshRkDStore-kpu YS
8
9
10Column1IDQtyAmountColumn1codelocationIDQtyAmount
11iliasA-Dates.551502.531.2iliasAs23cityA-Dates.551502.531.2
12A-A5001500gr1.5685iliasAs23cityA-A5001500gr1.5685
13SalimB-da13354.851.26salimS14store-Ahh RSWB-da13354.851.26
14c-Alm1.2430salimS14store-Ahh RSWc-Alm1.2430
15RakeshD-grap 1000gr2.5130rakeshRkDStore-kpu YSD-grap 1000gr2.5130
16E-cha3.51.25rakeshRkDStore-kpu YSE-cha3.51.25
17
Sheet1
Cell Formulas
RangeFormula
F10:K16F10=HSTACK(MAP(A2:C8,LAMBDA(x,LOOKUP("々",OFFSET(x,,,-ROW(x))))),B10:D16)
Dynamic array formulas.
 
Upvote 0
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.
 
Upvote 0
done -updated the excel version. Please if possible give me formula for Excel 2021.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,229
Messages
6,170,881
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top