Dec 17 2022 11:19 AM
Good day, I have a large amount of data (for example, I put 1000 lines of data, my normal file is 70 mb in size)I search the data in cells B9, C9 , D9 , E9 , F9 in Sheetst1 in the data sheet and call the same data from the cells A11 , B11 , C11 ,D11 , E11 , F11 in the datasu sheet but my file normally crashes because it's too big.I want to do this through ACCESS but I don't know how to do it. How can I load my data into ACCES application and call it from there to my sheets1? I have attached the sample file
Dec 17 2022 12:28 PM
Solution=IFERROR(INDEX(datasu!$E$2:$E$1000,SMALL(IF((datasu!$F$2:$F$1000=SHEETST1!$F$2)*(datasu!$G$2:$G$1000=SHEETST1!$G$2)*(datasu!$H$2:$H$1000=SHEETST1!$H$2)*(datasu!$I$2:$I$1000=SHEETST1!$I$2)*(datasu!$J$2:$J$1000=SHEETST1!$J$2),ROW(datasu!$E$2:$E$1000)-1),ROW($A1))),"")
An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. The formula is in cell H11 and filled down in the attached file.
=IFERROR(VLOOKUP($H11,datasu!$E$2:$J$1000,COLUMN(B$1),FALSE),"")
This formula is in cell I11 and filled across range I11:M19.
For the dropdown reference i've created lists of unique items in columns M to Q.
Which version of Excel do you work with and how many rows are there in your actual sheet? The formula should work well with thousands of rows.
Dec 17 2022 12:57 PM
You can create a table in a new Access database with the same fields as the table in the datasu sheet, and import or copy/paste the data.
See the attached sample database.
I imported the data and built a simple filter form.
Dec 17 2022 01:00 PM
my friend thank you very much for your help. is it possible to do this with acces, so I want to make an access connection with excel.I want my data that is in Access to be fetched according to the data on my sheetst1 page. @OliverScheurich
i'm sharing a link because access gave an error when uploading my file.I am also sharing the virus results of my file
https://dosya.co/os7l54d45ikh/Database1.accdb.html
VirusTotal - File - 35fe0702f36005f870976d6cae2000caaa1c6cdcc725ec5b4528eea93bb0a1ab
Dec 18 2022 04:20 AM
You are welcome. I don't have Access because i work with Office 2013 Home and Student. Therefore i can't help making an Access connection with Excel unfortunately.
Dec 19 2022 01:29 AM - edited Dec 19 2022 01:31 AM
as access is a relationship database,you can query data by sql like below。
select * from Sayfa1 where OBJECTID=1 and TANIM like 'main artery' and SERIT='2t' and CINS='asphalt' and SHAPE_Length=177;
Dec 17 2022 12:28 PM
Solution=IFERROR(INDEX(datasu!$E$2:$E$1000,SMALL(IF((datasu!$F$2:$F$1000=SHEETST1!$F$2)*(datasu!$G$2:$G$1000=SHEETST1!$G$2)*(datasu!$H$2:$H$1000=SHEETST1!$H$2)*(datasu!$I$2:$I$1000=SHEETST1!$I$2)*(datasu!$J$2:$J$1000=SHEETST1!$J$2),ROW(datasu!$E$2:$E$1000)-1),ROW($A1))),"")
An alternative could be this formula. Enter the formula with ctrl+shift+enter if you don't work with Office365 or Excel 2021. The formula is in cell H11 and filled down in the attached file.
=IFERROR(VLOOKUP($H11,datasu!$E$2:$J$1000,COLUMN(B$1),FALSE),"")
This formula is in cell I11 and filled across range I11:M19.
For the dropdown reference i've created lists of unique items in columns M to Q.
Which version of Excel do you work with and how many rows are there in your actual sheet? The formula should work well with thousands of rows.