Forum Discussion
excel and access
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
=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.
5 Replies
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.
=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.
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
https://www.virustotal.com/gui/file/35fe0702f36005f870976d6cae2000caaa1c6cdcc725ec5b4528eea93bb0a1ab?nocache=1
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;
