From table to one column/list

drag1c

Board Regular
Joined
Aug 7, 2019
Messages
92
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Code:
=IFERROR(INDEX($A$5:$A$99, MATCH(0, IF(LEN($A$5:$A$99), COUNTIF(J$4:J4, $A$5:$A$99), 1), 0)),
 IFERROR(INDEX($C$5:$C$99, MATCH(0, IF(LEN($C$5:$C$99), COUNTIF(J$4:J4, $C$5:$C$99), 1), 0)),
 IFERROR(INDEX($E$5:$E$99, MATCH(0, IF(LEN($E$5:$E$99), COUNTIF(J$4:J4, $E$5:$E$99), 1), 0)),
 IFERROR(INDEX($G$5:$G$99, MATCH(0, IF(LEN($G$5:$G$99), COUNTIF(J$4:J4, $G$5:$G$99), 1), 0)),
 ""))))

This code below shows only unique data. How to change it, so it shows all written data even if it is not unique?

Thank you !
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
What version of Excel are you using?

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’)
 
Upvote 0
Good day Mr. Fluff !

I am using 15.0.5327.1000 version of Professional 2013
 
Upvote 0
Thanks for that, what sort of values do you have in those columns?
Also do you have blank cells in those columns that should be ignored?
 
Upvote 0
Dear Mr. Fluff,
Yes, there are blank cells which should be ignored.
Here attached you could find list which I want to sort in a one row. We could say it is a bill of materials.
By picture, you could see there is black rectangle. These are borders of table which should be used in formula.

Thank you very much !
 

Attachments

  • Untitled.png
    Untitled.png
    47.4 KB · Views: 7
Upvote 0
Unfortunately, with data like that, I don't know of any way to do what you want with your version of Excel.
 
Upvote 0
Please, in kind of a matrix, avoid text cells. These are not important at all. Concerening numbers, these are clasiffied as "General".
Practicaly, we could avoid whole row 2.
Does it change something?

As I said, formula I gave on top gives me list with items, but it removes duplicates. How I can edit that formula to show duplicates? Problem is I dont understand how does MATCH part of formula works.

Here attached is small example how does formula works, on a right side of the sheet. It uses small "matrix" on top right side.
You could see how it removes duplicate. In this scenario, duplicate is "00000193#04". It has been shown just once, instead twice. Second time, it should be between rows 40 and 41.
 

Attachments

  • Untitled2.png
    Untitled2.png
    44.8 KB · Views: 5
Upvote 0
As I said, I don't know of any way to do what you want with the version of Xl that you are using.
 
Upvote 0
Here is solution:
This is array (ctrl + shift + enter)
Excel Formula:
=IF(ROWS(BOM!$1:1)>$T$1,"",INDIRECT(TEXT(SMALL(IF(billofmat<>"",10^5*ROW(billofmat)+COLUMN(billofmat)),ROWS(BOM!$1:1)),"R0C00000"),0))

In next previous/next column write:
Excel Formula:
=SUM(COUNTIF(billofmat,{">0","?*"}))

$T$1 pointing on 2nd formula place.

Works like a charm :)
 
Upvote 0
Solution

Forum statistics

Threads
1,213,482
Messages
6,113,913
Members
448,532
Latest member
9Kimo3

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