excel & array

  1. G

    Facing problem in finding Syntax for Advanced auto filter with wildcards using Macros

    I have created an event like when something changes in a cell, then an advanced filter should happen. The values to be filtered will be in C2:E3 with C2,D2,E2 as headers. the values will be filtered in B6:D67 with B6,C6,D6 as headers. Now the issue is: the values which I filter with...
  2. A

    VBA - AutoFilter by array doesnt work if list only has 1 or 0 rows of data. Big numbers are not filtered too.

    To start off, I posted the XL2BB sheets and VBA code below to help better visualize my problem. Basically, a macro is run each week to refresh the "FilterList" sheet that results in a varying number of rows of data. There could be 0 or more rows each time the macro is run. From here, I wrote...
  3. K


    How to marge 3 table into one table , because i face reference error , i tried......, pleased solving this query.... step by step.
  4. M

    Multiple Column Loop from wbA and copy/paste all data to 1 column on wkB

    Hello all, I'm trying to create an array loop that goes through columns A1,D1,G1, J1 (Keeping the headers) from one workbook and paste all the data to one column (keeping cell formatting) on another workbook. The data in the copied columns (A,D,G,J) will vary every time the macro runs so I'm...
  5. blackorchids2002

    Sort a 2 array of data

    Hi Masters, I need your help again. I was trying to use an index formula to sort the 2 sets of data from a smallest value. I cannot get it sorted how I want the data should look like in my excel. =IFERROR(INDEX(A$1:A$44,SMALL(IF($B$1:$B$44<>"",ROW($2:$44)-ROW($2:$2)+1),ROW($A2))),"") 1ST SET...
  6. K

    Query: How to count same party with unique Invoice numbers....

    like ... "SR FOODS LTD" two time make Purchase products. 1) how to count target Unique Invoice Number & same party Name ???????????????????????????????????????????????
  7. D

    Ideas on how to track inventory expiration dates

    NOTE* Please skip until the next line highlighted in bold if you're not interested in seeing how my setup functions. Thank you! Hello everybody, I am currently managing a minor inventory consisting of 101 unique goods. Currently my excel sheet looks like this: To briefly explain the sheet...
  8. P

    Combining repeated rows from 2 sheets' data based on a key

    I am having 2 sheets in excel and I am trying to combine the data such that for every record in Sheet1, I get records from Sheet2 based on the common key. Below is the sample data to clarify: I have SHEET 1 and SHEET 2 Data and I want to generate something like SHEET 3. Can you please suggest...
  9. V

    Concatenate Unique Values but remove the "/" where the valuue is unique

    Hi all, I need your guidance if possible on how to amend the below array formula? =TEXTJOIN("/", TRUE, IF(MATCH(AN4:AN23, AN4:AN23, 0)=MATCH(ROW(AN4:AN23), ROW(AN4:AN23)), AN4:AN23, "")) Currently it adds the "/" as a pefix to the end of all values but i need this removing so it shows like...
  10. A

    Excel to return a value in a column, if multiple criteria meets

    I tried to return a value in sheet1, column A (vertical), to sheet 2 (Horizontal format), if the "Process_Step" and "WO" is matched. As the data is huge and there're many columns, I do not know how to specifically to use the index and match or vlookup, to return the column A value (Date) if the...
  11. dreen

    Vlookup & Offset #Value Error

    I have two workbooks, one where I am doing the Vlookup and the other contains the table I need to retrieve the information from. I am trying to basically Vlookup the table found in the other workbook '[Database_IRR 200-2S.xlsm]Changes'! below row 3 as sometimes the lookup retrieves information...
  12. Salman98

    Convert a number stored as text to number

    Hello I have this array of numbers and alphabets stored as text as you may notice :{”6”;”4”;”1”;”D”;”R”} I want to convert the numbers to numbers and let the text stored as text, it may look like this: {6;4;1;”D”;”R”} is there a formula to work around that? Any help will be very appreciated...
  13. W

    VBA Code to loop through Array and with criteria

    I have below code where we arrived at the list based on criteria and I have assigned it to an array called arr1. Now I am looking to apply a filter in "K" colum to select "Yes" alone loop through this array of items which are in "I" column and get the total sum from R column. I am fine if array...
  14. P

    VBA code to Find Average

    Hi All, I have table data as below. There are 3 column. Vol1 CNT CNT_WISE_AVERAGE I want to write a VBA code to display the average value from "Vol1" column, based on "CNT value, and put the average value in "CNT_WISE_AVERAGE" column. There are nore then 5000 rows, having CNT values upto 80...
  15. R

    Finding and returning one of multiple potential values from a range

    Hi Everyone Unfortunately I cannot download XL2BB (work IT security), so I have had to use the below screenshot. I am working on a large formula and have got stuck on one component. For ease, I have broken out this component and simplified it as follows: I have 5 values: MYTPP; MAPTM; EGPSD...
  16. W

    Combining Columns with Dynamic Values

    Dear Pros, Please how can I use formula to create COLUMN C from A and B?
  17. L

    Formula Array Run-Time Error 1004

    Hi, I'm trying to insert this formula =IF(ISNA(INDEX(Pivot!$D:$D,MATCH('Schedule View'!$A4&'Schedule View'!$GS$2&'Schedule View'!GT$3,Pivot!$A:$A&Pivot!$E:$E&Pivot!$D:$D,0))),""No Commit Data"",INDEX(Pivot!$D:$D,MATCH('Schedule View'!$A4&'Schedule View'!$GS$2&'Schedule...
  18. G

    Excel Re-arranging data - row to column

    Hi, I have this data on excel SCALE_NO SCALE_NAME SIZ 1 DUAL 3942 1 DUAL 4346 1 DUAL L/XL 1 DUAL M/L 1 DUAL S/M 1 DUAL XS/S 2 ONE SIZE O/S 3 TALLA L 3 TALLA M 3 TALLA S 3 TALLA XL 3 TALLA XS 3 TALLA XXL 3 TALLA XXS if possible rearrange with some steps like...
  19. S

    Loop through multiple rows per iteration with Excel VBA

    How can I convert my Excel VBA code, which currently loops by each row, to loop through 10,000 sets, with each set containing 20 rows? I understand that the Step function might work, but I can't figure out how to amend my code to enable this. Basically, I would like to "paste" an array of 20...
  20. S

    Excel formula to Automatically put series on Patterns

    Hi, I'd like to ask help with excel. Is it possible to detect which comes first? example: John smith took his break 3 times a day, how can i identify the first break and the other 2, like is it possible that a formula can Make it like Break 1, Break 2, Break 3?

Some videos you may like

This Week's Hot Topics

  • Use Filter function with 2 criteria to return a specific value
    I've spent many hours trying to find out why the basic formula for Filter function with 2 criteria will not return a value other than #CALC. I'm...
  • Loop stopping at row 10
    Below is a code I had a lot of help with from user such as yourself. This code worked well but now is stopping a row 10 and I can't see why! Could...
  • Numerical Order Code for Macro
    I am trying to find a code or fix what i have on the macro that will automatically make the certain column generate in numerical order when I...
  • Calculate time in excel
    I get a text report from our time keeping system that i dump into excel through a data connection and I need to calculate the total hours for a...
  • VBA
    Hello everyone, I need your help please. I just need VBA code to get my reports working. What I want to do is whenever these dropdowns are...
  • Help with formula to sum numbers prior to date
    Hello, I was hoping to get some assistance as I'm having trouble with this. Using my table below as an example, how could I write a formula which...

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