How to sort the types of materials from one column according to position from other column

**El3cTr0** · 02-24-2021, 05:27 AM

I have in Page1, column B, 5 types of materials. In column L, I have numbered the position of each one.
So, Position 1 to 6, is 'Plastic tube', 7 to 11 is 'Textil Tube':

Screenshot 2021-02-24 at 11.17.52 AM.png

I need a formula, in Page 2, to summarize all of this, that can calculate this automatically, what kind of material is in each postion .

Like this:

Position :
1,2,3,4,5,6,15,16,17 - Plastic tube
7,8,9,10,11,19,20,21,22 - Textil tube
12,13,14 - Heat tube

I tried a lot with IF but not a good result. Not even close...

Anyone can help?

Thank you

**Richard Buttrey** · 02-24-2021, 07:00 AM

Hi,

It's not clear what results you expect, i.e what do YOU mean by summarise? Do you mean Count?

Please ADD the results you expect and reupload

**El3cTr0** · 02-24-2021, 07:26 AM

Hi,

Yes. I need it to count them.

The result that I'm looking for is in Page1 or below.

Screenshot 2021-02-24 at 1.25.09 PM.png

For each Position from Column L, what kind of material is in Column B

**Richard Buttrey** · 02-24-2021, 07:44 AM

This is still confusing. What you're showing is a list of positions, not a count of anything?

Are you in fact saying that the count results you want to know are 10 plastic tubes, (you seem to have missed out position 18), 9 Textil tubes, 3 Heat tubes

**El3cTr0** · 02-24-2021, 07:57 AM

No, All the positions from column L should appear. But I need to make the difference. What I sent, are just 22 position, normally I have hundreds.
The point is that I need to classify for each type of material(from column B) in what positions, the number(from column L) can be found.

**JohnTopley** · 02-24-2021, 08:30 AM

Are you saying from the "table" (data) in Sheet 1 you want to create the formatted table in Page 2 i.e fill in Column B with the article corresponding to those in Page1?

**El3cTr0** · 02-24-2021, 08:42 AM

Contrary. Only Page 2 is important. The Page 1 is for example, just for what formula should do.
From table from page 2 I need to get the result data from Page 1, a formula that can do what I did manually.

I have the table in front and I need to organize each type of material and write in what position can be found.

**Richard Buttrey** · 02-24-2021, 08:54 AM

I accept that language may be a problem here but I'm really struggling to understand the requirement.

You've mentioned the need to COUNT something but other than the totals on row 36, which presumably are not relevant to this task, I can't see any count of anything. In #5 you also mention 'differences' but again I've no idea what you expect.

Please clearly indicate with specific references to ranges
a) what data you start out with.
b) manually add the results you expect and say where those are, and explain in a note the calculations you have done to obtain the results.

**El3cTr0** · 02-24-2021, 09:15 AM

a) data that I start is the Page 2.
Attachment 720476

This is what I have and from this I need to start.

b) The result is in Page 1 and is:
Attachment 720478

The results are in Page 2. If I'm looking at Column L, I have numbered the positions, 1,2,3,4, etc... We'll this are the positions from a document. First position is Plastic tube, second Plastic tube, .... 7th Textil tube and so on.
Now, if in document I have 300-400 positions then I need to explain what type of material is in those positions. To show them Page 2 is too much, that's why I need to make it shorter.
For that I need a formula that can search for description and add the numbers from column L that can be found.

So, IF I search for Plastic tube to show 1,2,3,4,5,6,15,16,17.

What I tried is function IF. To search in Column B from description and then to show in what position can be found. But I don't know how to show the position .
EX:=if(left(Page2!B:B100,7) = "Plastic".......

I hope that I have explained myself...

**JohnTopley** · 02-24-2021, 10:37 AM

In Page2 .Helper column

M14

=IF($B14<>$B13,$M13+1,$M13)

copy down

In Page 1

D31

=INDEX(Page2!$B$14:$B$35,MATCH(ROWS($1:1),Page2!$M$14:$M$35,0))

E31

=INDEX(Page2!$L$14:$L$35,MATCH(ROWS($1:1),Page2!$M$14:$M$35,0))

F31

=COUNTIF(Page2!$M$14:$M$35,"<=" &ROWS($1:1))

G31

=$E31 & " - " & $F31

Copy all down

**El3cTr0** · 02-25-2021, 03:28 AM

Thank a lot. You saved a lot of my time.

Thank youuu

How to sort the types of materials from one column according to position from other column

LinkBack

Thread Tools

Rate This Thread

Display

How to sort the types of materials from one column according to position from other column

Re: How to sort the types of materials from one column according to position from other co

Re: How to sort the types of materials from one column according to position from other co

Re: How to sort the types of materials from one column according to position from other co

Re: How to sort the types of materials from one column according to position from other co

Re: How to sort the types of materials from one column according to position from other co

Re: How to sort the types of materials from one column according to position from other co

Re: How to sort the types of materials from one column according to position from other co

Re: How to sort the types of materials from one column according to position from other co

Re: How to sort the types of materials from one column according to position from other co

Re: How to sort the types of materials from one column according to position from other co

Thread Information

Users Browsing this Thread

Similar Threads

Excel: graphing a 3 colums table (column label, column date, column position)

Sort a selection by last column - or sort a range from column B onwards to last empty cell

[SOLVED] Button to Copy Column Range with ActiveCell position, Losing formatting after 3rd column

How can I sort 3 columns - Sort column A then sort column B then lastly group column C dup

Finding duplicates in column A and adding values in column B as the user types

How to sort by date column, which is not always at the same position?

Sort column containing different data types

Bookmarks

Bookmarks

Posting Permissions