Return multiple TEXT data

[ricdamiani]

Hi guys,

I am trying to return multiple text values and I just found in the internet for multiple numeric values.
I tried the formula below, but actually the formula SMALL just works for numeric values.

Please Login or Register  to view this content.

You can find the sheet attached below:
new production example 2.xlsx

So, what happen is some orders have different items, and this items are text (for example tube or strip).
The order number repeat in the first column when this order has more than 1 item.
What I need is to return the second, third.. (up to 10 items) of the repeated order numbers.

Any idea?

Thanks guys

[wenqq3]

I assume your sheet "SAP report" is always sorted ascending by SAP O/No.

Please Login or Register  to view this content.

[ricdamiani]

Great, it is working buddy
Thanks a lot.

Ps: it is "SOLVED" but I don't know how to change the status :]

[wenqq3]

SOLVED is inside my signature :D

If the problem is solved, please mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved".
Or
Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes

[ricdamiani]

Hi wenqq3,

Unfortunately I found a problem in the formula
The formula doesn't work if there is a blank row (or cell).
Please find the sheet attached demonstrating the problem:
Production - Copy.xlsm

SAP generates the report with 1 row with data and 1 row blank, 1 row with data and 1 row blank, and so forth.
There is a way to fix the formula and bring the data even if there are blank rows?

I appreciate your reply.
Thank you!

[wenqq3]

Will it always have 1 row blank after 1 row data?
And is the data always sorted?

[ricdamiani]

Thank you for the reply wenqq3.

Is not always but is 98% of the time.
I just extract another report from SAP and from the 130 rows with data, 127 are followed with a blank row. The other 3 rows came with data under them.

[ricdamiani]

And later I sort the data.

[wenqq3]

Please Login or Register  to view this content.

Can you try this.
This code worked if your data is always sorted and with 1 row data and 1 row blank.

[wenqq3]

I suggest you should make your data consistent.
If good if you use the 1st code i sent you, you just need sorted and remove the empty row.
remove empty row ( select col A and B, then ctrl + g, special, blank, right click selected blank row, delete, delete row)

[ricdamiani]

Hi wenqq3,

Ok, I will delete the rows as you described.
Thank you for that.

[ricdamiani]

I suggest you should make your data consistent.
If good if you use the 1st code i sent you, you just need sorted and remove the empty row.
remove empty row ( select col A and B, then ctrl + g, special, blank, right click selected blank row, delete, delete row)

Hi guys and wenqq3,

I would like to add one more sheet to compare with the master one. The formula now is the one below, but now it have to compare from "SAP Report" (is already in the formula) and from the tab "Orders" (which is not in the formula).

Please Login or Register  to view this content.

So if that number in the cell in the master sheet is not in "SAP Report" sheet, it needs to find in "Orders" sheet.

Is that possible to add?

Thanks guys

[ricdamiani]

Anybody can help me on that???

[ricdamiani]

UP

Somebody else can help?!

[wenqq3]

Hi ricdamiani, can you upload a sample workbook with expected result. Thanks

[ricdamiani]

Hi ricdamiani, can you upload a sample workbook with expected result. Thanks

Thanks for the reply mate

Please find sample sheet attached:
SAMPLE1308.xlsm

Looking forward to hear from you

[wenqq3]

Before:

Please Login or Register  to view this content.

After:

Please Login or Register  to view this content.

if you wish return something if found nothing in both sheets.
change the "" to some words example: "Found Nothing in both sheets"

Please Login or Register  to view this content.

[ricdamiani]

You are the man
Thanks dude, it is working

[wenqq3]

Great. Thanks for feedback!