• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need excel formulas

C

curiousone

New Member
I have two files. File 1 & File 2
File 1 has "Batch" field. There are unique batch numbers in that field.
File 2 has "Batch" field but batch numbers appear multiple times with it's own serial numbers.
I want serial numbers from file 2 to show up in file 1 in seperate columns on righr hand side.
For example, Batch 125963 in file 1 repeats itself 15 times in file 2 with 15 serial numbers. I want these 15 serial numbers to show into file 1, in columns F through T (15 columns. One serial number in one column). I want Excel formulas that would do this. The actual file are very large. So I need excel formulas. (No visual basic or Macros)
 

Attachments

  • File1.xlsx
    16.2 KB · Views: 3
  • File2.xlsx
    16.1 KB · Views: 2
Hi and welcome
please be aware that some courtesy is expected on a forum where you will get answers for free. "Hello" and "thank you" are not considered foul language. Cheers
 
curiousone said:
I have two files. File 1 & File 2
File 1 has "Batch" field. There are unique batch numbers in that field.
File 2 has "Batch" field but batch numbers appear multiple times with it's own serial numbers.
I want serial numbers from file 2 to show up in file 1 in seperate columns on righr hand side.
For example, Batch 125963 in file 1 repeats itself 15 times in file 2 with 15 serial numbers. I want these 15 serial numbers to show into file 1, in columns F through T (15 columns. One serial number in one column). I want Excel formulas that would do this. The actual file are very large. So I need excel formulas. (No visual basic or Macros)
Click to expand...
The 2 sentences in bold are somehow a bit contradictory. Excel formula on large files tend to be slow. Since the data is in 2 files, you need to use external references, which potentially make the formula rather long as well. And often both files need to be open before the formula "kicks in".

In a professional setting, I would opt here for a Power Query solution. Merge on Batch number, retain the serial numbers and pivot them.
 

Attachments

  • chandoo_55632.xlsx
    20.4 KB · Views: 5
You must log in or register to reply here.
Back
Top