Is it vlookup or if function or macro?

potter007

New Member
Joined
Aug 15, 2006
Messages
31
I'm trying to get a resulting answer but don't know which formula I need to use, from the data below, can anyone help?

Columns 1 and 2 is data that corresponds to each other and is sorted correctly.

Column 3 relates to columns 1 and 2 but I need to sort it against these columns by using the data in columns 4 and 5. I will also need blank rows putting in where data in column 3 is more than 1 of the same number.

Can this be done?


33300004 1 0046-0000UK-02 33300001 3C16670A
33300015 1 0327-00001U-04 33300002 3C16670A-UK
33300035 1 0407-GEDC00-01 33300003 3C16671
33300041 2 0427-GEACUK-01 33300004 3C16671A
33300044 1 2201AS0100XX 33300005 3C16671A-UK
33300045 1 2201AS0100XX 33300006 3C16672A
33300046 1 34000 33300007 3C16672A-UK
33300047 3 34001 33300008 3C16673
33300056 1 34001 33300009 3C16673B
33300059 1 34005 33300010 3C16680
33300060 1 34007 33300011 3C16681
33300121 1 3C16073 33300012 3C16683
33300129 1 3C16073 33300013 3C16684
33300132 1 3C16073 33300014 3C16685
33300133 1 3C16080 33300015 3C16700
33300157 1 3C16405 33300016 3C16700A
33300193 1 3C16405 33300017 3C16701
33300210 1 3C16406 33300018 3C16701A-UK
33300222 1 3C16406-UK 33300019 3C16702
33300223 1 3C16406-UK 33300020 3C16703A-UK
33300224 1 3C16406-UK 33300021 3C16704
33300226 3 3C16450 33300022 3C16710
33300235 1 3C16450 33300023 3C16710-UK
33300237 1 3C16630A 33300024 3C16721A
33300239 1 3C16630A 33300025 3C16723
33300251 1 3C16630A 33300026 3C16750
33300309 1 3C16665A-UK 33300027 3C16750B
33300314 1 3C16671A 33300028 3C16753
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
Hi there.

Can you clarify a couple of things please ?

1) What exactly is in each column ?
Is it like this -
Col 1 33300004
Col 2 1
Col 3 0046-0000UK-02
Col 4 33300001
Col 5 3C16670A
If it's not like this, what exactly does it look like ?

2) When you say
I will also need blank rows putting in where data in column 3 is more than 1 of the same number
I know ROUGHLY what you mean, but can you explain please EXACTLY what you mean ?
Rows 5 and 6 look as if they have the same entries in Col 3. So what exactly do you want the results to look like ?
 
Upvote 0
Hi there.

Can you clarify a couple of things please ?

1) What exactly is in each column ?
Is it like this -
Col 1 33300004
Col 2 1
Col 3 0046-0000UK-02
Col 4 33300001
Col 5 3C16670A
If it's not like this, what exactly does it look like ?

2) When you say

I know ROUGHLY what you mean, but can you explain please EXACTLY what you mean ?
Rows 5 and 6 look as if they have the same entries in Col 3. So what exactly do you want the results to look like ?

Thanks for replying, I did put spaces between them, but when it posted it bunched them together.

Your bullet point 1 is correct, that's the correct column breakdown.

Bullet point 2, rows in column 1 and 2 will not change, when the sort is done, where a code in column 3 has more than 1 of the same, I will need a blank cell to go in column 1 and 2 so that the following cells and codes match.

e.g

column 1 column 2 column 3
33300004 2 0046-0000uk-02
0046-0000uk-02
33300005 1 0046-0000uk-03
33300006 3 0046-0000uk-04
0046-0000uk-04
0046-0000uk-04
33300007 1 0046-0000uk-05

Hope this makes sense, as it's frustrating not knowing how to put it across in a way people can understand when hoping for an answer.
 
Upvote 0
OK thanks. There may be better ways to do this, but I would consider a pivot table for this - I think it can do what you need.

Post back if you're not sure how to make a pivot table do this.
 
Upvote 0
OK.

Sorry Potter007 but I need to go back a stage.

Re-reading this I'm still a bit confused.
In your post #3, you imply that this number
0046-0000uk-02
appears twice in the data.

But it doesn't appear twice in your post #1.

I referred in my first post to rows 5 and 6, which look as if they are the same in Col 3.
Can you tell us EXACTLY what should happen with those two rows ?

Also, post #1 talks about 5 columns, but in post #3, this has become only 3 columns.
What happened to the other two - can we ignore them ?

Also, it looks as if the numbers in Columns 1 and 4 are related, perhaps the same thing. Do you need them to be matched up in some way ?
In which case, which column is accurately matched to Column 3 - is it Col 1 or Col 4 ?

I don't mean to sound pedantic, I just want to make sure I fully understand what you want, before I get too far into writing out a solution.
 
Upvote 0
OK.

Sorry Potter007 but I need to go back a stage.

Re-reading this I'm still a bit confused.
In your post #3, you imply that this number
0046-0000uk-02
appears twice in the data.

But it doesn't appear twice in your post #1.

I referred in my first post to rows 5 and 6, which look as if they are the same in Col 3.
Can you tell us EXACTLY what should happen with those two rows ?

Also, post #1 talks about 5 columns, but in post #3, this has become only 3 columns.
What happened to the other two - can we ignore them ?

Also, it looks as if the numbers in Columns 1 and 4 are related, perhaps the same thing. Do you need them to be matched up in some way ?
In which case, which column is accurately matched to Column 3 - is it Col 1 or Col 4 ?

I don't mean to sound pedantic, I just want to make sure I fully understand what you want, before I get too far into writing out a solution.

I was trying to save time in writing in post 3.

The 5 five columns still stand.

Columns 1 and 2 data are already matched
column 3 is data that needs to be matched to columns 1 and 2 and where the data in column 3 has more than 1 code of the same, a blank row will need to be put in columns 1 and 2 to reflect this.
columns 4 and 5 data match and these are the columns that column 3 needs to use to get the correct code that matches to column 1.


This is a nightmare to put into words, wish to god I could just point and say that's what I need.
 
Upvote 0
OK. So please can you manually put the data that is in rows 1-6 in your first post into exactly the format you want it to be when it is finished, and post that here.
 
Upvote 0
This is where my problem lies. The only data that needs to be shown is columns 1,2 and 3.
Columns 4 and 5 are lookup references for column 3 to get the corresponding code for column 1

I have 3000 rows of data to sort and match, which I can't post all of that.

Currently the rows in columns 1 and 2 are matched.

Column 3 needs to be matched to the code in column 1 by using columns 4 and 5 as a lookup tool.

I thank you for your time you are taking to help, I know this must be frustrating as for you as it is for me.

Is there a way I can post a better viewing of my excel sheet and the data on it to help explain more?
 
Upvote 0
I'm not asking you to post all 3000 rows, just the first 6.

There are several ways of posting Excel data - look at the posting guidelines.
But if you can't use them, just post them into a post, like you did in post 1.

Don't worry about columns 4 and 5 that won't be shown - include them in your post, so that it's clear how they link to the other columns. We can then hide them later on, once we've got the linking sorted out.

Please post how you want rows 1 to 6 to appear, showing all 5 columns.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,243
Members
448,555
Latest member
RobertJones1986

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