Return multiple values horizontally.

**SamCV** · 06-05-2014, 05:53 PM

Hello to all -

I managed to found an array formula that helps to return multiple values with a condition. Below the formula:

Formula:

{=IFERROR(INDEX($J$4:$J$8, SMALL(IF($N4=$H$4:$H$8, ROW($H$4:$H$8)-MIN(ROW($H$4:$H$8))+1, ""), COLUMN(A$1))),"")}

I am attaching a simple file. In cell O4 I paste the formula, and I drag it as much as I need. But my issue comes when the formula begins the calculations. I need to calculate 50 columns with over 9,000 rows each column (over 45,000 arrays). Imagine the processing load to my computer and the consuming of time.

I want your help if there is a simpler formula to work with that allow me to obtain the same result.

I prefer formula instead of VBA. I know VBA Codes are of great help, but I can not record VBA codes in my computer because of Office Security Settings.

I will appreciate any help that you can give me.

Best regards to all and please have a great day.

Samuel Cruz.
Matamoros, Mexico.

**k64** · 06-05-2014, 06:20 PM

Not sure what the best way is to do this. Here are a couple ideas to help.
1. Sort your list and then use a helper column to keep track of where the values switch, and use that to do your indexing
2. Do your calculation once and then copy and paste values so it doesn't keep updating.

**daffodil11** · 06-05-2014, 06:33 PM

I have a workbook with 294,000 rows, each with a 9-criteria array calculating spherical trigonemetry stuff; it's processor murder.
It literally forced me to learn VB just so I could get things accomplished in my day instead of waiting for things to finish evaluating. It cut 45 minute processing down to exactly 83 seconds.

Here's a pretty neat idea, but it only works if you don't have duplicate price values:

Select all of your data
Insert-> Pivot Table
Row Labels - Your things
Column Labels - Your prices
Values - Sum of Price
Copy/Paste Values of Pivot Table
Select all of the prices
Hit F5
Special
Select Blanks
Hit Okay
Go to Cells in the Toolbar
Expand the Delete Button
Choose Cells
Collapse Left

**FDibbins** · 06-05-2014, 07:18 PM

Sam, what are those links for on sheet1?

**SamCV** · 06-06-2014, 07:38 AM

Hello FDibbins...

Thank you for reply. Those are from the page where I found the formula that I mentioned. I believed I had deleted them. I adapted the formula to my ranges in Sheet1 (2). Very sorry if I introduce the links, but I wanted to show how the original formula was in case of need. So sorry for not mentioning.

Samuel Cruz.
Matamoros, Mexico.

**samba_ravi** · 06-06-2014, 01:11 AM

Create a supporting column and use Vlookup or Index Match formula
see the attachement

**SamCV** · 06-06-2014, 07:50 AM

Originally Posted by nflsales

Create a supporting column and use Vlookup or Index Match formula
see the attachement

Hello nflsales.
I will give it a try. I'll let you know how it worked.

Best regards!!
Samuel Cruz.
Matamoros, Mexico.

Return multiple values horizontally.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Return multiple values horizontally.

Re: Return multiple values horizontally.

Re: Return multiple values horizontally.

Re: Return multiple values horizontally.

Re: Return multiple values horizontally.

Re: Return multiple values horizontally.

Re: Return multiple values horizontally.

Thread Information

Users Browsing this Thread

Similar Threads

Excel 2010 - Looking up one value and need to return mutiple values horizontally

[SOLVED] Formula to lookup and return multiple results horizontally sheet 1 and 2

Lookup to return multiple values horizontally

[SOLVED] Return multiple values horizontally using vlookup

return multiple values horizontally while removing duplicates

Bookmarks

Bookmarks

Posting Permissions